package com.wxtf.platform.main.tjbb;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.net.URLDecoder;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellStyle;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.AccessDataBaseUtil;
import com.wxtf.platform.tools.Common;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.FormatUtil;

/**
 * 
 * <li>Title: Baobiao2Action.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.tjbb</li>
 * <li>Description: 人口与计划生育统计报表2</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 12, 2012 9:50:44 AM</li>
 *
 * @author bai_xin
 * @version 1.0.0.0
 *
 */
public class Baobiao2Action extends LogicBase implements CommonsCallProvider {
	private static Log log = LogFactory.getLog(Baobiao2Action.class);
	
	public Baobiao2Action(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
			} else if("querySubOrg".equals(this.getMethodName())) {
				msg = this.querySubOrg(this.getLogicData());
			} else if("queryEmployee".equals(this.getMethodName())) {
				msg = this.queryEmployee(this.getLogicData());
			} else if("queryEmployeeFamily".equals(this.getMethodName())) {
				msg = this.queryEmployeeFamily(this.getLogicData());
			} else if("querySearch".equals(this.getMethodName())) {
				msg = this.querySearch(this.getLogicData());
			} else if("save".equals(this.getMethodName())) {
				msg = this.insert(this.getLogicData());
			} else if("update".equals(this.getMethodName())) {
				msg = this.update(this.getLogicData());
			} else if("delete".equals(this.getMethodName())) {
				msg = this.delete(this.getLogicData());
			} else if("download".equals(this.getMethodName())) {
				msg = this.download(this.getLogicData());
			} else if("sendback".equals(this.getMethodName())) {
				msg = this.sendback(this.getLogicData());
			} else if("alert".equals(this.getMethodName())) {
				msg = this.alert(this.getLogicData());
			} else if("pickup".equals(this.getMethodName())) {
				msg = this.pickup(this.getLogicData());
			} else{
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	 
	
	String[] bb_fields = {
			"bb_id","date_month","date_day","tbr_name","tbr_tel","org_id","bb_type","bb_year","bb_quarter",
			"e_zgrs_hj", "e_zgrs_nan","e_zgrs_nv","e_hyzk_jhrs","e_hyzk_whrs","e_hyzk_whl","e_syqk_csyes","e_syqk_qz_jhnyt",
			"e_syqk_qz_jhnet","e_syqk_qz_jhwcs","e_syqk_qz_wyrs","e_syqk_qz_wyl","e_syqk_qz_jhsyl","e_syqk_qz_hyrs","e_dsznqk_yingbanzheng",
			"e_dsznqk_yibanzheng","e_dsznqk_bzl","e_dsznqk_xydszns","e_ssqk_rl","e_ssqk_sh","e_ssqk_jz","e_ssqk_yc_zq","e_ssqk_yc_dyf","e_blzsyzms",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time","Children","org_name"
	};
	
	String[] employee_fields = {
			"tbe_id","org_id","tbe_idcard","tbe_work_permit",
			"tbe_employment_forms","tbe_name","tbe_former_name","tbe_spell_code","tbe_sex","tbe_age","tbe_birthday","tbe_nation","tbe_native_palce",
			"tbe_birthplace","tbe_join_time","tbe_seniority","tbe_family_phone","tbe_mobile","tbe_address","tbe_marital_status","tbe_marital_condition",
			"tbe_married_time","tbe_marriage_certificate","tbe_political_status","tbe_qualification","tbe_graduate_college","tbe_major","tbe_work_area",
			"tbe_position","tbe_is_again_birth","tbe_is_barrenness","the_is_menopause","tbe_is_late_marriage","tbe_is_late_birth","tbe_status","tbe_ba_status",
			"tbe_birth_control_measures","tbe_es_time","tbe_is_es","tbe_es_reason","tbe_children_status","tbe_order","tbe_memo",
			"reserve1","reserve2","reserve3","reserve4","reserve5","create_by","create_time","last_modify_by","last_modify_time"
	};
	
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	/**
	 * 
	 * 描述：报表上报（保存报表数据）
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	@Override
	public Object insert(Object jsonArray) throws Exception {
		//获取当前用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//获取logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		
		// 首先查看当前季度的报表二是否已经上报，如果没有上报，则提示需先上报当前季度的报表二
		int type = jsonObject.getInt("type");
		if(type == 1){
			String orgId = jsonObject.getString("org_id");
			StringBuffer baobiao1Sql = new StringBuffer("select count(*) from tb_rkyjhsy_baobiao1 where bb_type='").append(jsonObject.getString("type")).append("' and bb_year=").append(jsonObject.getString("year")).append(" and bb_quarter=").append(jsonObject.getString("quarter")).append(" and org_id='").append(orgId).append("'");
			int bb1 = AccessDataBaseUtil.queryForInt(baobiao1Sql.toString(), null);
			if(bb1 == 0){
				return "{\"status\":\"failure\",\"message\":\"上报失败：请先上报表一，再上报表二！\"}";
			}
		}
		
		String subOrgIds = this.getSubIdsSql(user.getCurOrgId());
    	//判断自部门是否已经全部上报
		int subOrgCount = this.getSubOrgCount(user.getCurOrgId());
		//如果subOrgCount大于0，说明有子机构，需要判断是否子机构已经全部上报
		if(subOrgCount > 0){
			int subOrgSubmitCount = this.getSubOrgSubmitCount(subOrgIds, jsonObject.getString("year"), jsonObject.getString("quarter"));
			//如果子机构数量和子机构已上报报表数量不相等，则认为有子机构尚未上报数据，不能继续上报
			if(subOrgCount != subOrgSubmitCount){
				return "{\"status\":\"failure\",\"message\":\"目前无法上报，有子机构的报表未上报，所有子机构上报完成后才可以继续上报！\"}";
			}
		}
		
		//从logicdata中取出上报数据
		JSONArray dataRows = jsonObject.getJSONArray("data");
		
		JSONObject jobj = null;
		List<String> sqlList = new ArrayList<String>();
		//循环所有上报数据，生成相应SQL并装填在一个List<String>对象中
		for(Object obj : dataRows){
			jobj = JSONObject.fromObject(obj);
			StringBuffer sql = new StringBuffer();
			sql.append("insert into tb_rkyjhsy_baobiao2 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms,create_by,create_time) ");
			sql.append("values (");
			
			sql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
			sql.append(jsonObject.getString("date_month")).append(",");
			sql.append(jsonObject.getString("date_day")).append(",");
			sql.append("'").append(jsonObject.getString("tbr")).append("',");
			sql.append("'").append(jsonObject.getString("tel")).append("',");
			sql.append("'").append(jobj.getString("org_id")).append("',");
			sql.append("'").append(jsonObject.getString("type")).append("',");
			sql.append(jsonObject.getString("year")).append(",");
			sql.append(jsonObject.getString("quarter")).append(",");
			
			sql.append(jobj.getString("e_zgrs_hj")).append(",");
			sql.append(jobj.getString("e_zgrs_nan")).append(",");
			sql.append(jobj.getString("e_zgrs_nv")).append(",");
			sql.append(jobj.getString("e_hyzk_jhrs")).append(",");
			sql.append(jobj.getString("e_hyzk_whrs")).append(",");
			sql.append(jobj.getString("e_hyzk_whl")).append(",");
			sql.append(jobj.getString("e_syqk_csyes")).append(",");
			sql.append(jobj.getString("e_syqk_qz_jhnyt")).append(",");
			sql.append(jobj.getString("e_syqk_qz_jhnet")).append(",");
			sql.append(jobj.getString("e_syqk_qz_jhwcs")).append(",");
			sql.append(jobj.getString("e_syqk_qz_wyrs")).append(",");
			sql.append(jobj.getString("e_syqk_qz_wyl")).append(",");
			sql.append(jobj.getString("e_syqk_qz_jhsyl")).append(",");
			sql.append(jobj.getString("e_syqk_qz_hyrs")).append(",");
			sql.append(jobj.getString("e_dsznqk_yingbanzheng")).append(",");
			sql.append(jobj.getString("e_dsznqk_yibanzheng")).append(",");
			sql.append(jobj.getString("e_dsznqk_bzl")).append(",");
			sql.append(jobj.getString("e_dsznqk_xydszns")).append(",");
			sql.append(jobj.getString("e_ssqk_rl")).append(",");
			sql.append(jobj.getString("e_ssqk_sh")).append(",");
			sql.append(jobj.getString("e_ssqk_jz")).append(",");
			sql.append(jobj.getString("e_ssqk_yc_zq")).append(",");
			sql.append(jobj.getString("e_ssqk_yc_dyf")).append(",");
			sql.append(jobj.getString("e_blzsyzms")).append(",");
			
			sql.append("'").append(user.getUserId()).append("',");
			sql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
			
			sql.append(")");
			sqlList.add(sql.toString());
			sqlList.add("update tbe_neonate_main set reserve5='" + jobj.getString("org_id") + "-" + jsonObject.getString("year") + "-" + jsonObject.getString("quarter") + "' where org_id='" + jobj.getString("org_id") + "' and reserve5='' ");
			sqlList.add("update tbc_surgery_record set reserve5='" + jobj.getString("org_id") + "-" + jsonObject.getString("year") + "-" + jsonObject.getString("quarter") + "' where org_id='" + jobj.getString("org_id") + "' and reserve5='' ");
			sqlList.add("update tbe_faa_birth_record set reserve5='" + jobj.getString("org_id") + "-" + jsonObject.getString("year") + "-" + jsonObject.getString("quarter") + "' where org_id='" + jobj.getString("org_id") + "' and reserve5='' ");
			sqlList.add("update tb_employee set reserve5='" + jobj.getString("org_id") + "-" + jsonObject.getString("year") + "-" + jsonObject.getString("quarter") + "' where org_id='" + jobj.getString("org_id") + "' and reserve5='1' ");
		}
		//批量插入数据库
		AccessDataBaseUtil.executeSQLUpdate(sqlList);
		
		return "{\"status\":\"success\",\"message\":\"上报成功！\"}";
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	/**
	 * 
	 * 描述：查询报表数据
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	@Override
	public Object select(Object jsonArray) throws Exception {
		//获取logicdata对象
		JSONArray array = JSONArray.fromObject(jsonArray);
		//获取当前操作用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//获取当前和子部门的ID字符串并解析成字符串数组
		String[] orgids = user.getCurSubOrgId().split(",");
		//获取当前部门ID
		String curOrgId = user.getCurOrgId();
		//判断logicdata是否有数据
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			//从logicdata中取出相应的参数
			//type报表类型 1：季度 2：年度
			int type = jsonObject.getInt("type");
			//年份
			int year = jsonObject.getInt("year");
			//季度
			int quarter = jsonObject.getInt("quarter");

			// 排除不计算的人员
			/*
			员工状态：emp.tbe_status
			102-3	102		已离职	员工状态
			102-4	102		已退休	员工状态
			102-5	102		死亡	     员工状态
			用工形式：emp.tbe_employment_forms
			31	101		劳务用工	用工形式
			*/
			String notCountEmployeeSql = " emp.tbe_employment_forms!='31' and emp.tbe_status!='102-3' and emp.tbe_status!='102-4' and emp.tbe_status!='102-5' ";
		    if(orgids.length == 1){
		       	// 判断当前人员所在机构是否有子机构，如果没有，则为最基层机构
		       	// 最基层机构的数据需要从各个表中取得
		       	
		       	// 首先从表中查找该部门本段（季度或年度）是否已经上报了数据
		       	String querySql = "select bb.*,'0',d.caption from tb_rkyjhsy_baobiao2 bb , t_department d where bb.org_id=d.department_id and bb.org_id='" + user.getCurOrgId() + "' and bb.bb_year=" + year + " and bb.bb_quarter=" + quarter;
		       	List<Map<String, Object>> bbRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, querySql, null);
		       	if(bbRecordList.size() > 0){
		       		// 如果bbRecordList.size() > 0，则表明该部门是否已经上报了数据，
		       		// 则直接从表中取出并返回
		       		Map<String, Object> bbMap = new HashMap<String, Object>();
		       		Map<String, Object> globleMap = bbRecordList.get(0);
		       		bbMap.put("Total", "1");
		       		bbMap.put("tbr_name", globleMap.get("tbr_name"));
		       		bbMap.put("tbr_tel", globleMap.get("tbr_tel"));
		       		bbMap.put("date_month", globleMap.get("date_month"));
		       		bbMap.put("date_day", globleMap.get("date_day"));
		       		bbMap.put("Shangbao", "0");
		       		bbMap.put("Rows", bbRecordList);
		       		bbMap.put("Sum", bbRecordList.get(0));
		       		
		       		JSONObject object = JSONObject.fromObject(bbMap);
					return object.toString();
		       	}else{
		       		//格式化小数
		       		DecimalFormat df = new DecimalFormat("0.##");
		       		// 如果是年度报表
		       		if(type == 2){
		       			//(select sum(e_bjcs) from tb_rkyjhsy_baobiao1 where org_id='" + user.getCurOrgId() + "' and bb_year=" + year + ")
		       			StringBuffer nianQuerySql = new StringBuffer("select bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,")
			       			.append("e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,")
			       			.append("(select sum(e_hyzk_jhrs) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_hyzk_whrs) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("e_hyzk_whl,")
			       			.append("(select sum(e_syqk_csyes) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_syqk_qz_jhnyt) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_syqk_qz_jhnet) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_syqk_qz_jhwcs) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_syqk_qz_wyrs) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("0,'100.00',e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,")
			       			.append("e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,")
			       			
			       			.append("(select sum(e_ssqk_rl) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_ssqk_sh) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_ssqk_jz) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_ssqk_yc_zq) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_ssqk_yc_dyf) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("(select sum(e_blzsyzms) from tb_rkyjhsy_baobiao2 where org_id='")
			       			.append(user.getCurOrgId()).append("' and bb_year=").append(year).append(" and bb_type=1),")
			       			.append("reserve1,reserve2,reserve3,reserve4,reserve5,create_by,create_time,last_modify_by,last_modify_time,'0',d.caption from tb_rkyjhsy_baobiao2 bb left join t_department d on bb.org_id=d.department_id where bb.org_id='")
			       			.append(user.getCurOrgId()).append("' and bb.bb_year=").append(year).append(" and bb.bb_quarter=4");
		       			List<Map<String, Object>> bbNianRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, nianQuerySql.toString(), null);
				       	if(bbNianRecordList.size() > 0){
				       		// 如果bbRecordList.size() > 0，则表明该部门是否已经上报了数据，
				       		// 则直接从表中取出并返回
				       		Map<String, Object> bbMap = new HashMap<String, Object>();
				       		Map<String, Object> globleMap = bbNianRecordList.get(0);
				       		// 计算年度晚育率
				       		int e_syqk_qz_jhnyt = Integer.parseInt(globleMap.get("e_syqk_qz_jhnyt").toString());
				       		int e_syqk_qz_wyrs = Integer.parseInt(globleMap.get("e_syqk_qz_wyrs").toString());
				       		String e_syqk_qz_wyl = e_syqk_qz_jhnyt == 0 ? df.format(0) : df.format((float)(e_syqk_qz_wyrs * 100)/e_syqk_qz_jhnyt);
				       		globleMap.put("e_syqk_qz_wyl", e_syqk_qz_wyl);
				       		bbNianRecordList.set(0, globleMap);
				       		
				       		bbMap.put("Total", "1");
				       		bbMap.put("tbr_name", globleMap.get("tbr_name"));
				       		bbMap.put("tbr_tel", globleMap.get("tbr_tel"));
				       		bbMap.put("date_month", globleMap.get("date_month"));
				       		bbMap.put("date_day", globleMap.get("date_day"));
				       		bbMap.put("Shangbao", "1");
				       		bbMap.put("Rows", bbNianRecordList);
				       		bbMap.put("Sum", globleMap);
				       		
				       		JSONObject object = JSONObject.fromObject(bbMap);
							return object.toString();
				       	}else{
				       		return "{\"Total\":\"0\",\"message\":\"请先将每季度数据上报完成后，再能上报年度报表！\",\"Rows\":[]}";
				       	}
		       		}
		       		// 不是年度报表，就是季度报表
		       		// 如果当前机构尚未上报本段（季度或年度）的报表
		       		// 则从数据库中各个里取出数据，并加以运算
					/*
					业务项：
					逻辑关系：业务项中字段由数字代表，按照规定的数字运算情况，显示表中的统计数字。
					1、	结婚人数（男+女）=本季度婚姻状态从未婚调成为已婚（婚姻状况为初婚）的人数（男+女）
					2、	晚婚人数（男+女）登记婚姻状态时是否晚婚选项勾选的职工。
					3、	出生婴儿数=本季内新出生登记数。
					4、	计划内一胎=本季内新出生登记时勾选：“计划内一胎”选项的新生儿。
					5、	计划内二胎=本季内新出生登记时勾选：“计划内二胎”选项的新生儿。
					6、	晚育人数=登记时勾选“是否晚育”选项的新生儿。
					7、	晚育率=晚育人数/出生婴儿数*100%
					8、	计划生育率= （计划内一胎+计划内二胎）/计划外出生*100%
					9、	怀孕人数=怀孕登记的人数（截止到本季度末状态是怀孕登记的人数） 注释：怀孕后生产或者流产、引产的职工需要及时注销怀孕状态。
					10、	已办证职工（男+女）：截止到本季度末已经办理独生子女光荣证的员工。
					11、	应办证职工（男+女）：家庭内只有一个孩子（双胞胎都都除外）的员工。
					12、	现有独生子女数：统计家庭内只有一个孩子的明细数量。
					13、	引产中期和大月份：妊娠大于28周以上进行妊娠终止手术为引产大月份，妊娠小于28周大于16周以上进行妊娠终止手术为中期引产。小于16周进行妊娠终止的行为是人流手术。
					14、	办理再生育证明数：本季度内办理再生育登记的员工。
					*/
		       		String nvlFunction = "";
		        	if("mysql".equals(user.getDbType())){
		        		nvlFunction = "ifnull(";
		        	}else{
		        		nvlFunction = "nvl(";
		        	}
		       		
			       	StringBuffer sqlsb = new StringBuffer();
			       	//1 职工人数 合计
			       	sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and ").append(notCountEmployeeSql).append(" union all ");
			       	//2 职工人数 男职工
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='男' and ").append(notCountEmployeeSql).append(" union all "); 
					//3 职工人数 女职工
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='女' and ").append(notCountEmployeeSql).append(" union all ");
					//4 婚姻状况 结婚人数 结婚时间：emp.tbe_married_time
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.reserve5='1' and ").append(notCountEmployeeSql).append(" union all ");
					//5 婚姻状况 晚婚人数 是否晚婚：emp.tbe_is_late_marriage
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.reserve5='1' and emp.tbe_is_late_marriage='1' and ").append(notCountEmployeeSql).append(" union all ");
					//6 婚姻状况 晚婚率 （晚婚人数/结婚人数）
					sqlsb.append( "select 0 from dual union all ");
					
					//7 生育情况 出生婴儿数
					sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
					//8 生育情况 其中 计划内一胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
					sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='0' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
					//9 生育情况 其中 计划内二胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
					sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='1' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
					//10 生育情况 其中 计划外出生 计划：tbenm.tbenm_plan_status 由于不允许有计划外出生，所以这里默认为0 
					sqlsb.append( "select 0 from dual union all ");
					//11 生育情况 其中 晚育人数 是否晚婚+晚育tbenm.reserve4
					//sqlsb.append( "select count(tbenm.tbenm_id) from tbe_neonate_main tbenm where tbenm.reserve4='1' and tbenm.org_id='" ).append( curOrgId ).append( "' union all ");
					sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='0' and tbenm.reserve5='' and tbenm.reserve4='1' and ").append(notCountEmployeeSql).append(" union all ");
					//12 生育情况 其中 晚育率（这里先取生育人数，后面通过晚育人数/计划内一胎）
					sqlsb.append( "select 0 from dual union all ");
					//13 生育情况 其中 计划生育率(计划内一胎 + 计划内二胎)/出生婴儿数 由于不允许有计划外出生，所以这里默认为100.00
					sqlsb.append( "select 100 from dual union all ");
					//14 生育情况 其中 怀孕人数
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbepr.tbepr_option='0' and ").append(notCountEmployeeSql).append(" union all ");

					//15 独生子女情况 应办证职工数
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and fb.tbfi_children_num=1 and ").append(notCountEmployeeSql).append(" union all ");
					//16 独生子女情况 已办证职工数
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_only_child_record tbeocr where emp.tbe_id=fb.tbe_id and fb.tbfi_id=tbeocr.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbeocr.reserve2!='1' and ").append(notCountEmployeeSql).append(" union all ");
					//17 独生子女情况 办证率(已办证职工数/应办证职工数 * 100%)
					sqlsb.append( "select 0 from dual union all ");
					//18 独生子女情况 现有独生子女数 家庭子女数:fb.tbfi_children_num
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and fb.tbfi_valid='1' and fb.tbfi_children_num=1 and ").append(notCountEmployeeSql).append(" union all ");
					
					/*
					手术类型
					109-1	109		皮埋	手术类型
					109-2	109		上环:取环	手术类型
					109-3	109		结扎	手术类型
					110-1	110		人流	手术类型
					110-2	110		引产(中期)
					110-3	110		引产(大月份)
					*/
					//19 四术情况 人流
					sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-1' and ").append(notCountEmployeeSql).append(" union all ");
					//20 四术情况 上环
					sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='109-2' and ").append(notCountEmployeeSql).append(" union all ");
					//21 四术情况 结扎
					sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='109-3' and ").append(notCountEmployeeSql).append(" union all ");
					//22 四术情况 引产 中期
					sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-2' and ").append(notCountEmployeeSql).append(" union all ");
					//23 四术情况 引产 大月份
					sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-3' and ").append(notCountEmployeeSql).append(" union all ");
					//24 办理再生育证明数
					sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_faa_birth_record tbefbr where ((emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='男') or (emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='0') or (emp.tbe_id=fb.tbe_spouse_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='1')) and fb.tbfi_id=tbefbr.tbfi_id and tbefbr.tbefbr_type='1' and emp.org_id='").append(curOrgId).append("' and tbefbr.reserve5='' and ").append(notCountEmployeeSql);

					List<Object> list = AccessDataBaseUtil.query4List(sqlsb.toString(), null);
					if(list == null || list.size() == 0){
						return "{\"Total\":\"0\",\"message\":\"数据查询过程出现异常，请联系系统管理员！\",\"Rows\":[]}";
					}
					
					Map<String, Object> map = new HashMap<String, Object>();
					Map<String, Object> map1 = new HashMap<String, Object>();
					
					
					map1.put("org_id", user.getCurOrgId());
					map1.put("org_name", user.getCurOrgName());
					map1.put("e_zgrs_hj", list.get(0).toString());
					map1.put("e_zgrs_nan", list.get(1).toString());
					map1.put("e_zgrs_nv", list.get(2).toString());
					map1.put("e_hyzk_jhrs", list.get(3).toString());
					map1.put("e_hyzk_whrs", list.get(4).toString());
					
					int jhrs = Integer.parseInt(list.get(3).toString());
					int whrs = Integer.parseInt(list.get(4).toString());
					map1.put("e_hyzk_whl", jhrs == 0 ? df.format(0.00) : df.format((float)(whrs * 100)/jhrs));
					
					map1.put("e_syqk_csyes", list.get(6).toString());
					map1.put("e_syqk_qz_jhnyt", list.get(7).toString());
					map1.put("e_syqk_qz_jhnet", list.get(8).toString());
					map1.put("e_syqk_qz_jhwcs", list.get(9).toString());
					map1.put("e_syqk_qz_wyrs", list.get(10).toString());
					
					int csyes = Integer.parseInt(list.get(6).toString());
					int wyrs = Integer.parseInt(list.get(10).toString());
					int jhnyt = Integer.parseInt(list.get(7).toString());
					map1.put("e_syqk_qz_wyl", jhnyt == 0 ? df.format(0.00) : df.format((float)(wyrs * 100)/jhnyt));
					
					map1.put("e_syqk_qz_jhsyl", csyes == 0 ? df.format(100.00) : df.format(100.00));
					map1.put("e_syqk_qz_hyrs", list.get(13).toString());
					map1.put("e_dsznqk_yingbanzheng", list.get(14).toString());
					map1.put("e_dsznqk_yibanzheng", list.get(15).toString());
					
					int yingbanzheng = Integer.parseInt(list.get(14).toString());
					int yibanzheng = Integer.parseInt(list.get(15).toString());
					map1.put("e_dsznqk_bzl", yingbanzheng == 0 ? df.format(0.00) : df.format((float)(yibanzheng * 100)/yingbanzheng));

					map1.put("e_dsznqk_xydszns", list.get(17).toString());
					map1.put("e_ssqk_rl", list.get(18).toString());
					map1.put("e_ssqk_sh", list.get(19).toString());
					map1.put("e_ssqk_jz", list.get(20).toString());
					map1.put("e_ssqk_yc_zq", list.get(21).toString());
					map1.put("e_ssqk_yc_dyf", list.get(22).toString());
					map1.put("e_blzsyzms", list.get(23).toString());
					map1.put("Children", "0");
					
					List<Map<String, Object>> rowsList = new ArrayList<Map<String, Object>>();
					rowsList.add(map1);
					
					map.put("Total", "1");
					map.put("tbr_name", user.getUserrelName());
					map.put("tbr_tel", "");
					map.put("Shangbao", "1");
					map.put("Rows", rowsList);
					map.put("Sum", map1);
					JSONObject object = JSONObject.fromObject(map);
					return object.toString();
	        	}
	        }else{
	        	// 如果当前机构有子机构，那么则从统计报表中取出子机构的数据并加以统计
	        	String nvlSql = "";
	        	if("mysql".equals(user.getDbType())){
	        		nvlSql = "ifnull(bb.bb_id,0),ifnull(bb.date_month,0),ifnull(bb.date_day,0),ifnull(bb.tbr_name,0),ifnull(bb.tbr_tel,0),d.department_id,ifnull(bb.bb_type,0),ifnull(bb.bb_year,0),ifnull(bb.bb_quarter,0),ifnull(bb.e_zgrs_hj,0),ifnull(bb.e_zgrs_nan,0),ifnull(bb.e_zgrs_nv,0),ifnull(bb.e_hyzk_jhrs,0),ifnull(bb.e_hyzk_whrs,0),ifnull(bb.e_hyzk_whl,0),ifnull(bb.e_syqk_csyes,0),ifnull(bb.e_syqk_qz_jhnyt,0),ifnull(bb.e_syqk_qz_jhnet,0),ifnull(bb.e_syqk_qz_jhwcs,0),ifnull(bb.e_syqk_qz_wyrs,0),ifnull(bb.e_syqk_qz_wyl,0),ifnull(bb.e_syqk_qz_jhsyl,0),ifnull(bb.e_syqk_qz_hyrs,0),ifnull(bb.e_dsznqk_yingbanzheng,0),ifnull(bb.e_dsznqk_yibanzheng,0),ifnull(bb.e_dsznqk_bzl,0),ifnull(bb.e_dsznqk_xydszns,0),ifnull(bb.e_ssqk_rl,0),ifnull(bb.e_ssqk_sh,0),ifnull(bb.e_ssqk_jz,0),ifnull(bb.e_ssqk_yc_zq,0),ifnull(bb.e_ssqk_yc_dyf,0),ifnull(bb.e_blzsyzms,0),ifnull(bb.reserve1,0),ifnull(bb.reserve2,0),ifnull(bb.reserve3,0),ifnull(bb.reserve4,0),ifnull(bb.reserve5,0),ifnull(bb.create_by,0),ifnull(bb.create_time,0),ifnull(bb.last_modify_by,0),ifnull(bb.last_modify_time,0) ";
	        	}else{
	        		nvlSql = "nvl(bb.bb_id,0),nvl(bb.date_month,0),nvl(bb.date_day,0),nvl(bb.tbr_name,0),nvl(bb.tbr_tel,0),d.department_id,nvl(bb.bb_type,0),nvl(bb.bb_year,0),nvl(bb.bb_quarter,0),nvl(bb.e_zgrs_hj,0),nvl(bb.e_zgrs_nan,0),nvl(bb.e_zgrs_nv,0),nvl(bb.e_hyzk_jhrs,0),nvl(bb.e_hyzk_whrs,0),nvl(bb.e_hyzk_whl,0),nvl(bb.e_syqk_csyes,0),nvl(bb.e_syqk_qz_jhnyt,0),nvl(bb.e_syqk_qz_jhnet,0),nvl(bb.e_syqk_qz_jhwcs,0),nvl(bb.e_syqk_qz_wyrs,0),nvl(bb.e_syqk_qz_wyl,0),nvl(bb.e_syqk_qz_jhsyl,0),nvl(bb.e_syqk_qz_hyrs,0),nvl(bb.e_dsznqk_yingbanzheng,0),nvl(bb.e_dsznqk_yibanzheng,0),nvl(bb.e_dsznqk_bzl,0),nvl(bb.e_dsznqk_xydszns,0),nvl(bb.e_ssqk_rl,0),nvl(bb.e_ssqk_sh,0),nvl(bb.e_ssqk_jz,0),nvl(bb.e_ssqk_yc_zq,0),nvl(bb.e_ssqk_yc_dyf,0),nvl(bb.e_blzsyzms,0),nvl(bb.reserve1,0),nvl(bb.reserve2,0),nvl(bb.reserve3,0),nvl(bb.reserve4,0),nvl(bb.reserve5,0),nvl(bb.create_by,0),nvl(bb.create_time,0),nvl(bb.last_modify_by,0),nvl(bb.last_modify_time,0) ";
	        	}
	        	String subOrgIds = this.getSubIdsSql(user.getCurOrgId());
	        	//取出所有子部门的数据
	        	StringBuffer querySql = new StringBuffer("select ").append(nvlSql).append(",(select count(*) from t_department where dparent_id=d.department_id), d.caption from t_department d left join tb_rkyjhsy_baobiao2 bb on (d.department_id=bb.org_id and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(") where d.department_id in(").append(subOrgIds).append(") order by d.ordernum ");
	        	List<Map<String, Object>> bbRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, querySql.toString(), null);
	        	//取出本部门数据（主要取出辅助信息如：日期、填表人、联系电话等等）
	        	String curOrgRecordSql = "select bb.date_month,bb.date_day,bb.tbr_name,bb.tbr_tel from tb_rkyjhsy_baobiao2 bb where bb.org_id='" + user.getCurOrgId() + "' and bb.bb_year=" + year + " and bb.bb_quarter=" + quarter;
	        	List<Object[]> curOrgRecordList = AccessDataBaseUtil.query(curOrgRecordSql, null);
	        	
	        	if(bbRecordList == null || bbRecordList.size() == 0){
		       		return "{\"Total\":\"0\",\"message\":\"数据查询过程出现异常，请联系系统管理员！\",\"Rows\":[]}";
				}
	        	
	        	//定义合计数据
	        	int sum_e_zgrs_hj = 0;
				int sum_e_zgrs_nan = 0;
				int sum_e_zgrs_nv = 0;
				int sum_e_hyzk_jhrs = 0;
				int sum_e_hyzk_whrs = 0;
				String sum_e_hyzk_whl = "100.00";
				int sum_e_syqk_csyes = 0;
				int sum_e_syqk_qz_jhnyt = 0;
				int sum_e_syqk_qz_jhnet = 0;
				int sum_e_syqk_qz_jhwcs = 0;
				int sum_e_syqk_qz_wyrs = 0;
				String sum_e_syqk_qz_wyl = "100.00";
				String sum_e_syqk_qz_jhsyl = "100.00";
				int sum_e_syqk_qz_hyrs = 0;
				int sum_e_dsznqk_yingbanzheng = 0;
				int sum_e_dsznqk_yibanzheng = 0;
				String sum_e_dsznqk_bzl = "100.00";
				int sum_e_dsznqk_xydszns = 0;
				int sum_e_ssqk_rl = 0;
				int sum_e_ssqk_sh = 0;
				int sum_e_ssqk_jz = 0;
				int sum_e_ssqk_yc_zq = 0;
				int sum_e_ssqk_yc_dyf = 0;
				int sum_e_blzsyzms = 0;
	        	
	        	// 循环所有子部门数据计算合计
	        	for(Map<String, Object> bbRecordMap : bbRecordList){
	        		sum_e_zgrs_hj += Integer.parseInt(bbRecordMap.get("e_zgrs_hj").toString());
	        		sum_e_zgrs_nan += Integer.parseInt(bbRecordMap.get("e_zgrs_nan").toString());
	        		sum_e_zgrs_nv += Integer.parseInt(bbRecordMap.get("e_zgrs_nv").toString());
	        		sum_e_hyzk_jhrs += Integer.parseInt(bbRecordMap.get("e_hyzk_jhrs").toString());
	        		sum_e_hyzk_whrs += Integer.parseInt(bbRecordMap.get("e_hyzk_whrs").toString());
	        		sum_e_syqk_csyes += Integer.parseInt(bbRecordMap.get("e_syqk_csyes").toString());
	        		sum_e_syqk_qz_jhnyt += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhnyt").toString());
	        		sum_e_syqk_qz_jhnet += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhnet").toString());
	        		sum_e_syqk_qz_jhwcs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhwcs").toString());
	        		sum_e_syqk_qz_wyrs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_wyrs").toString());
	        		sum_e_syqk_qz_hyrs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_hyrs").toString());
	        		sum_e_dsznqk_yingbanzheng += Integer.parseInt(bbRecordMap.get("e_dsznqk_yingbanzheng").toString());
	        		sum_e_dsznqk_yibanzheng += Integer.parseInt(bbRecordMap.get("e_dsznqk_yibanzheng").toString());
	        		sum_e_dsznqk_xydszns += Integer.parseInt(bbRecordMap.get("e_dsznqk_xydszns").toString());
	        		sum_e_ssqk_rl += Integer.parseInt(bbRecordMap.get("e_ssqk_rl").toString());
	        		sum_e_ssqk_sh += Integer.parseInt(bbRecordMap.get("e_ssqk_sh").toString());
	        		sum_e_ssqk_jz += Integer.parseInt(bbRecordMap.get("e_ssqk_jz").toString());
	        		sum_e_ssqk_yc_zq += Integer.parseInt(bbRecordMap.get("e_ssqk_yc_zq").toString());
	        		sum_e_ssqk_yc_dyf += Integer.parseInt(bbRecordMap.get("e_ssqk_yc_dyf").toString());
	        		sum_e_blzsyzms += Integer.parseInt(bbRecordMap.get("e_blzsyzms").toString());
	        	}
				DecimalFormat df = new DecimalFormat("0.##");//格式化小数
				sum_e_hyzk_whl = sum_e_hyzk_jhrs == 0 ? df.format(0) : df.format((float)(sum_e_hyzk_whrs * 100)/sum_e_hyzk_jhrs);
				sum_e_syqk_qz_wyl = sum_e_syqk_qz_jhnyt == 0 ? df.format(0) : df.format((float)(sum_e_syqk_qz_wyrs * 100)/sum_e_syqk_qz_jhnyt);
				sum_e_dsznqk_bzl = sum_e_dsznqk_yingbanzheng == 0 ? df.format(0) : df.format((float)(sum_e_dsznqk_yibanzheng * 100)/sum_e_dsznqk_yingbanzheng);
				sum_e_syqk_qz_jhsyl = sum_e_syqk_csyes == 0 ? df.format(100) : df.format(100); 
				//定义合计Map
				Map<String, Object> sumMap = new HashMap<String, Object>();
				//向合计Map里装填数据
				sumMap.put("e_zgrs_hj", sum_e_zgrs_hj);
				sumMap.put("e_zgrs_nan",sum_e_zgrs_nan);
				sumMap.put("e_zgrs_nv",sum_e_zgrs_nv);
				sumMap.put("e_hyzk_jhrs",sum_e_hyzk_jhrs);
				sumMap.put("e_hyzk_whrs",sum_e_hyzk_whrs);
				sumMap.put("e_hyzk_whl",sum_e_hyzk_whl);
				sumMap.put("e_syqk_csyes",sum_e_syqk_csyes);
				sumMap.put("e_syqk_qz_jhnyt",sum_e_syqk_qz_jhnyt);
				sumMap.put("e_syqk_qz_jhnet",sum_e_syqk_qz_jhnet);
				sumMap.put("e_syqk_qz_jhwcs",sum_e_syqk_qz_jhwcs);
				sumMap.put("e_syqk_qz_wyrs",sum_e_syqk_qz_wyrs);
				sumMap.put("e_syqk_qz_wyl",sum_e_syqk_qz_wyl);
				sumMap.put("e_syqk_qz_jhsyl",sum_e_syqk_qz_jhsyl);
				sumMap.put("e_syqk_qz_hyrs",sum_e_syqk_qz_hyrs);
				sumMap.put("e_dsznqk_yingbanzheng",sum_e_dsznqk_yingbanzheng);
				sumMap.put("e_dsznqk_yibanzheng",sum_e_dsznqk_yibanzheng);
				sumMap.put("e_dsznqk_bzl",sum_e_dsznqk_bzl);
				sumMap.put("e_dsznqk_xydszns",sum_e_dsznqk_xydszns);
				sumMap.put("e_ssqk_rl",sum_e_ssqk_rl);
				sumMap.put("e_ssqk_sh",sum_e_ssqk_sh);
				sumMap.put("e_ssqk_jz",sum_e_ssqk_jz);
				sumMap.put("e_ssqk_yc_zq",sum_e_ssqk_yc_zq);
				sumMap.put("e_ssqk_yc_dyf",sum_e_ssqk_yc_dyf);
				sumMap.put("e_blzsyzms",sum_e_blzsyzms);
	        	
	        	Map<String, Object> bbMap = new HashMap<String, Object>();
	       		//装入辅助信息
	        	if(curOrgRecordList != null && curOrgRecordList.size() > 0){
	       			Object[] curOrgRecord = curOrgRecordList.get(0);
	        		bbMap.put("date_month", curOrgRecord[0].toString());
	        		bbMap.put("date_day", curOrgRecord[1].toString());
	        		bbMap.put("tbr_name", curOrgRecord[2].toString());
	        		bbMap.put("tbr_tel", curOrgRecord[3].toString());
	        		bbMap.put("Shangbao", "0");
	       		}else{
	        		bbMap.put("tbr_name", user.getUserrelName());
	        		bbMap.put("tbr_tel", "");
	       			bbMap.put("Shangbao", "1");
	       		}
	       		
	       		bbMap.put("Total", bbRecordList.size());
	       		bbMap.put("Rows", bbRecordList);
	       		bbMap.put("Sum", sumMap);
	       		//生成JSON字符串并输出返回
	       		JSONObject object = JSONObject.fromObject(bbMap);
				return object.toString();
	        }
		}
	
		return null;
	}
	
	/**
	 * 
	 * 描述：查询子机构报表数据
	 * @param 
	 * @return
	 * @throws Exception
	 * @CreateOn Oct 24, 2012 9:47:03 AM
	 * @author bai_xin
	 */
	public Object querySubOrg(Object jsonArray) throws Exception {
		JSONArray array = JSONArray.fromObject(jsonArray);
		//获取当前操作用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String orgId = jsonObject.getString("org_id");
			int type = jsonObject.getInt("type");
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			String subOrgIds = this.getSubIdsSql(orgId);
			String nvlSql = "";
			if("mysql".equals(user.getDbType())){
        		nvlSql = "ifnull(bb.bb_id,0),ifnull(bb.date_month,0),ifnull(bb.date_day,0),ifnull(bb.tbr_name,0),ifnull(bb.tbr_tel,0),d.department_id,ifnull(bb.bb_type,0),ifnull(bb.bb_year,0),ifnull(bb.bb_quarter,0),ifnull(bb.e_zgrs_hj,0),ifnull(bb.e_zgrs_nan,0),ifnull(bb.e_zgrs_nv,0),ifnull(bb.e_hyzk_jhrs,0),ifnull(bb.e_hyzk_whrs,0),ifnull(bb.e_hyzk_whl,0),ifnull(bb.e_syqk_csyes,0),ifnull(bb.e_syqk_qz_jhnyt,0),ifnull(bb.e_syqk_qz_jhnet,0),ifnull(bb.e_syqk_qz_jhwcs,0),ifnull(bb.e_syqk_qz_wyrs,0),ifnull(bb.e_syqk_qz_wyl,0),ifnull(bb.e_syqk_qz_jhsyl,0),ifnull(bb.e_syqk_qz_hyrs,0),ifnull(bb.e_dsznqk_yingbanzheng,0),ifnull(bb.e_dsznqk_yibanzheng,0),ifnull(bb.e_dsznqk_bzl,0),ifnull(bb.e_dsznqk_xydszns,0),ifnull(bb.e_ssqk_rl,0),ifnull(bb.e_ssqk_sh,0),ifnull(bb.e_ssqk_jz,0),ifnull(bb.e_ssqk_yc_zq,0),ifnull(bb.e_ssqk_yc_dyf,0),ifnull(bb.e_blzsyzms,0),ifnull(bb.reserve1,0),ifnull(bb.reserve2,0),ifnull(bb.reserve3,0),ifnull(bb.reserve4,0),ifnull(bb.reserve5,0),ifnull(bb.create_by,0),ifnull(bb.create_time,0),ifnull(bb.last_modify_by,0),ifnull(bb.last_modify_time,0) ";
        	}else{
        		nvlSql = "nvl(bb.bb_id,0),nvl(bb.date_month,0),nvl(bb.date_day,0),nvl(bb.tbr_name,0),nvl(bb.tbr_tel,0),d.department_id,nvl(bb.bb_type,0),nvl(bb.bb_year,0),nvl(bb.bb_quarter,0),nvl(bb.e_zgrs_hj,0),nvl(bb.e_zgrs_nan,0),nvl(bb.e_zgrs_nv,0),nvl(bb.e_hyzk_jhrs,0),nvl(bb.e_hyzk_whrs,0),nvl(bb.e_hyzk_whl,0),nvl(bb.e_syqk_csyes,0),nvl(bb.e_syqk_qz_jhnyt,0),nvl(bb.e_syqk_qz_jhnet,0),nvl(bb.e_syqk_qz_jhwcs,0),nvl(bb.e_syqk_qz_wyrs,0),nvl(bb.e_syqk_qz_wyl,0),nvl(bb.e_syqk_qz_jhsyl,0),nvl(bb.e_syqk_qz_hyrs,0),nvl(bb.e_dsznqk_yingbanzheng,0),nvl(bb.e_dsznqk_yibanzheng,0),nvl(bb.e_dsznqk_bzl,0),nvl(bb.e_dsznqk_xydszns,0),nvl(bb.e_ssqk_rl,0),nvl(bb.e_ssqk_sh,0),nvl(bb.e_ssqk_jz,0),nvl(bb.e_ssqk_yc_zq,0),nvl(bb.e_ssqk_yc_dyf,0),nvl(bb.e_blzsyzms,0),nvl(bb.reserve1,0),nvl(bb.reserve2,0),nvl(bb.reserve3,0),nvl(bb.reserve4,0),nvl(bb.reserve5,0),nvl(bb.create_by,0),nvl(bb.create_time,0),nvl(bb.last_modify_by,0),nvl(bb.last_modify_time,0) ";
        	}
			StringBuffer querySb = new StringBuffer("select ").append(nvlSql).append(",(select count(*) from t_department where dparent_id=d.department_id), d.caption from t_department d left join tb_rkyjhsy_baobiao2 bb on (d.department_id=bb.org_id and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(") where d.department_id in(").append(subOrgIds).append(") order by d.ordernum ");
        	List<Map<String, Object>> bbRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, querySb.toString(), null);
        	Map<String, Object> bbMap = new HashMap<String, Object>();
    		
    		bbMap.put("Total", bbRecordList.size());
    		bbMap.put("Rows", bbRecordList);
    		
    		JSONObject object = JSONObject.fromObject(bbMap);
			return object.toString();
			
		}
		return null;
	}
	
	/**
	 * 
	 * 描述：查询符合指定条件的员工信息
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	public Object queryEmployee(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			int column = jsonObject.getInt("column");
			String orgId = jsonObject.getString("org_id");
			int type = jsonObject.getInt("type");
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			int shangbao = jsonObject.getInt("shangbao");
			// 定义读取按季度统计的数据区分类型SQL
			String empReserveSql = "";
			String tbcsrReserveSql = "";
			if(type == 1){
				// 如果当前部门和数据所属部门是同一个单位
				if(user.getCurOrgId().equals(orgId)){
					if(shangbao == 0){
						empReserveSql = " emp.reserve5 like '%" + year + "-" + quarter + "' ";
						tbcsrReserveSql = " tbcsr.reserve5 like '%" + year + "-" + quarter + "' ";
					}else{
						empReserveSql = " emp.reserve5='1' ";
						tbcsrReserveSql = " tbcsr.reserve5='' ";
					}
				}else{
					// 如果是不同单位，说明是上级查看下级数据
					empReserveSql = " emp.reserve5 like '%" + year + "-" + quarter + "' ";
					tbcsrReserveSql = " tbcsr.reserve5 like '%" + year + "-" + quarter + "' ";
				}
			}else{
				empReserveSql = " (emp.reserve5 like '%" + year + "-1' or emp.reserve5 like '%" + year + "-2' or emp.reserve5 like '%" + year + "-3' or emp.reserve5 like '%" + year + "-4') ";
				tbcsrReserveSql = " (tbcsr.reserve5 like '%" + year + "-1' or tbcsr.reserve5 like '%" + year + "-2' or tbcsr.reserve5 like '%" + year + "-3' or tbcsr.reserve5 like '%" + year + "-4') ";
			}
			// 计算育龄日期段
			StringBuffer sqlsb = new StringBuffer();
			String ids = this.getAllSubIdsSql(orgId);
			String idsSql;
			if(StringUtils.isBlank(ids)){
				idsSql = "='" + orgId + "'";
			}else{
				idsSql = "in(" + ids + ")";
			}
			switch(column){
				//职工人数 合计
			    case 3 : sqlsb.append( " from tb_employee emp where emp.org_id " ).append( idsSql );break;
		       	//职工人数 男职工
			    case 4 : sqlsb.append( " from tb_employee emp where emp.tbe_sex='男' and emp.org_id " ).append( idsSql );break;
				//职工人数 女职工
			    case 5 : sqlsb.append( " from tb_employee emp where emp.tbe_sex='女' and emp.org_id " ).append( idsSql );break;
				//婚姻状况 结婚人数 结婚时间：emp.tbe_married_time
			    case 6 : sqlsb.append( " from tb_employee emp where emp.org_id " ).append( idsSql ).append( " and ").append(empReserveSql);break;
				//婚姻状况 晚婚人数 是否晚婚：emp.tbe_is_late_marriage
			    case 7 : sqlsb.append( " from tb_employee emp where emp.org_id " ).append( idsSql ).append( " and ").append(empReserveSql).append(" and emp.tbe_is_late_marriage='1' ");break;
			    
			    //独生子女情况 应办证职工数
			    case 17 : sqlsb.append( " from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id " ).append( idsSql ).append( " and fb.tbfi_children_num=1 ");break;
				//独生子女情况 已办证职工数
			    case 18 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_only_child_record tbeocr where emp.tbe_id=fb.tbe_id and fb.tbfi_id=tbeocr.tbfi_id and tbeocr.reserve2!='1' and emp.org_id " ).append( idsSql );break;
				
				//四术情况 人流
			    case 21 : sqlsb.append( " from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id " ).append( idsSql ).append( " and ").append(tbcsrReserveSql).append(" and tbcsr.tbcsr_type='110-1' ");break;
				//四术情况 上环
			    case 22 : sqlsb.append( " from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id " ).append( idsSql ).append( " and ").append(tbcsrReserveSql).append(" and tbcsr.tbcsr_type='109-2' ");break;
				//四术情况 结扎
			    case 23 : sqlsb.append( " from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id " ).append( idsSql ).append( " and ").append(tbcsrReserveSql).append(" and tbcsr.tbcsr_type='109-3' ");break;
				//四术情况 引产 中期
			    case 24 : sqlsb.append( " from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id " ).append( idsSql ).append( " and ").append(tbcsrReserveSql).append(" and tbcsr.tbcsr_type='110-2' ");break;
				//四术情况 引产 大月份
			    case 25 : sqlsb.append( " from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id " ).append( idsSql ).append( " and ").append(tbcsrReserveSql).append(" and tbcsr.tbcsr_type='110-3' ");break;
				
			    
			}
			sql = sqlsb.append(" and emp.tbe_employment_forms!='31' and emp.tbe_status!='102-3' and emp.tbe_status!='102-4' and emp.tbe_status!='102-5' ").toString();
			//sql = sqlsb.toString();
		}
		
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by emp." + sortname + " " + sortorder;
		}
		
		String countSql = "select count(emp.tbe_id) " + sql;
		String querySql = "select emp.tbe_id,emp.org_id,emp.tbe_idcard,emp.tbe_work_permit,emp.tbe_employment_forms,emp.tbe_name,emp.tbe_former_name,emp.tbe_spell_code,emp.tbe_sex,emp.tbe_age,emp.tbe_birthday,emp.tbe_nation,emp.tbe_native_palce,emp.tbe_birthplace,emp.tbe_join_time,emp.tbe_seniority,emp.tbe_family_phone,emp.tbe_mobile,emp.tbe_address,emp.tbe_marital_status,emp.tbe_marital_condition,emp.tbe_married_time,emp.tbe_marriage_certificate,emp.tbe_political_status,emp.tbe_qualification,emp.tbe_graduate_college,emp.tbe_major,emp.tbe_work_area,emp.tbe_position,emp.tbe_is_again_birth,emp.tbe_is_barrenness,emp.the_is_menopause,emp.tbe_is_late_marriage,emp.tbe_is_late_birth,emp.tbe_status,emp.tbe_ba_status,emp.tbe_birth_control_measures,emp.tbe_es_time,emp.tbe_is_es,emp.tbe_es_reason,emp.tbe_children_status,emp.tbe_order,emp.tbe_memo,emp.reserve1,emp.reserve2,emp.reserve3,emp.reserve4,emp.reserve5,emp.create_by,emp.create_time,emp.last_modify_by,emp.last_modify_time " + sql;
		return AccessDataBaseUtil.query4SQLPagging(employee_fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	/**
	 * 
	 * 描述：查询符合指定条件的家庭信息
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	public Object queryEmployeeFamily(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");

		Object[] params = null;
		
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			int column = jsonObject.getInt("column");
			String orgId = jsonObject.getString("org_id");
			int type = jsonObject.getInt("type");
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			int shangbao = jsonObject.getInt("shangbao");
			// 定义读取按季度统计的数据区分类型SQL
			String tbenmReserveSql = "";
			String tbefbrReserveSql = "";
			if(type == 1){
				// 如果当前部门和数据所属部门是同一个单位
				if(user.getCurOrgId().equals(orgId)){
					if(shangbao == 0){
						tbenmReserveSql = " tbenm.reserve5 like '%" + year + "-" + quarter + "' ";
						tbefbrReserveSql = " tbefbr.reserve5 like '%" + year + "-" + quarter + "' ";
					}else{
						tbenmReserveSql = " tbenm.reserve5='' ";
						tbefbrReserveSql = " tbefbr.reserve5='' ";
					}
				}else{
					// 如果是不同单位，说明是上级查看下级数据
					tbenmReserveSql = " tbenm.reserve5 like '%" + year + "-" + quarter + "' ";
					tbefbrReserveSql = " tbefbr.reserve5 like '%" + year + "-" + quarter + "' ";
				}
			}else{
				tbenmReserveSql = " (tbenm.reserve5 like '%" + year + "-1' or tbenm.reserve5 like '%" + year + "-2' or tbenm.reserve5 like '%" + year + "-3' or tbenm.reserve5 like '%" + year + "-4') ";
				tbefbrReserveSql = " (tbefbr.reserve5 like '%" + year + "-1' or tbefbr.reserve5 like '%" + year + "-2' or tbefbr.reserve5 like '%" + year + "-3' or tbefbr.reserve5 like '%" + year + "-4') ";
			}
			StringBuffer sqlsb = new StringBuffer();
			String ids = this.getAllSubIdsSql(orgId);
			String idsSql;
			if(StringUtils.isBlank(ids)){
				idsSql = "='" + orgId + "'";
			}else{
				idsSql = "in(" + ids + ")";
			}
			switch(column){
				//生育情况 出生婴儿数
			    case  9 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id " ).append( idsSql ).append( " and ").append(tbenmReserveSql);break;
				//生育情况 其中 计划内一胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
			    case 10 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id " ).append( idsSql ).append( " and tbenm.tbenm_plan_status='0' and " ).append(tbenmReserveSql);break;
				//生育情况 其中 计划内二胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
			    case 11 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id " ).append( idsSql ).append( " and tbenm.tbenm_plan_status='1' and " ).append(tbenmReserveSql);break;
				//生育情况 其中 计划外出生 计划：tbenm.tbenm_plan_status 由于不允许有计划外出生，所以这里默认为0 
			    //case 12 : sqlsb.append( " from dual union all ");break;
				//生育情况 其中 晚育人数 是否晚婚+晚育tbenm.reserve4
			    case 13 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id " ).append( idsSql ).append( " and tbenm.tbenm_plan_status='0' and tbenm.reserve4='1' and ").append(tbenmReserveSql);break;

				//生育情况 其中 怀孕人数
			    case 16 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and emp.org_id " ).append( idsSql ).append( " and tbepr.tbepr_option='0' " );break;
				//独生子女情况 现有独生子女数 家庭子女数:fb.tbfi_children_num
			    case 20 : sqlsb.append( " from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id " ).append( idsSql ).append( " and fb.tbfi_valid='1' and fb.tbfi_children_num=1 ");break;
				
			    //办理再生育证明数
			    case 26 : sqlsb.append( " from tb_employee emp , tb_family_base fb , tbe_faa_birth_record tbefbr where ((emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='男') or (emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='0') or (emp.tbe_id=fb.tbe_spouse_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='1')) and fb.tbfi_id=tbefbr.tbfi_id and tbefbr.tbefbr_type='1' and emp.org_id ").append(idsSql).append(" and ").append(tbefbrReserveSql);break;
				
			}
			sql = sqlsb.append(" and emp.tbe_employment_forms!='31' and emp.tbe_status!='102-3' and emp.tbe_status!='102-4' and emp.tbe_status!='102-5' ").toString();
		}
		
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			if("org_name".equals(sortname)){
				sql += " order by emp.reserve1 " + sortorder;
			} else {
				sql += " order by emp." + sortname + " " + sortorder;
			}
		}

		String countSql = "select count(emp.tbe_id) " + sql;

		String querySql = "select emp.tbe_id,emp.org_id,emp.reserve1 as org_name,emp.tbe_idcard,emp.tbe_work_permit,emp.tbe_name,emp.tbe_sex,emp.tbe_age,emp.tbe_birthday,emp.tbe_position,emp.tbe_native_palce,emp.tbe_family_phone,emp.tbe_address,emp.tbe_marital_status,emp.tbe_marital_condition,emp.tbe_is_again_birth,emp.tbe_is_barrenness,emp.tbe_is_late_marriage,emp.tbe_is_late_birth,emp.tbe_status,fb.tbfi_id,fb.tbe_spouse_id,fb.tbfi_spouse_name,fb.tbfi_spouse_sex,fb.tbfi_spouse_age,fb.tbfi_spouse_birthday,fb.tbfi_spouse_idcard,fb.tbfi_spouse_department,fb.tbfi_spouse_position,fb.tbfi_spouse_is_join,fb.tbfi_phone,fb.tbfi_mobile,fb.tbfi_address,fb.tbfi_peo_num,fb.tbfi_children_num,fb.tbfi_children_mnum,fb.tbfi_children_fnum,fb.tbfi_memo,fb.reserve1,fb.reserve2,fb.reserve3,fb.reserve4,fb.reserve5,fb.create_by,fb.create_time,fb.last_modify_by,fb.last_modify_time " + sql;
		String[] fields = { "tbe_id", "org_id", "org_name", "tbe_idcard", "tbe_work_permit", "tbe_name", "tbe_sex",
				"tbe_age", "tbe_birthday","tbe_position", "tbe_native_palce", "tbe_family_phone", "tbe_address", "tbe_marital_status",
				"tbe_marital_condition", "tbe_is_again_birth", "tbe_is_barrenness", "tbe_is_late_marriage",
				"tbe_is_late_birth", "tbe_status", "tbfi_id", "tbe_spouse_id", "tbfi_spouse_name", "tbfi_spouse_sex",
				"tbfi_spouse_age", "tbfi_spouse_birthday", "tbfi_spouse_idcard", "tbfi_spouse_department",
				"tbfi_spouse_position", "tbfi_spouse_is_join", "tbfi_phone", "tbfi_mobile", "tbfi_address",
				"tbfi_peo_num", "tbfi_children_num", "tbfi_children_mnum", "tbfi_children_fnum", "tbfi_memo",
				"reserve1", "reserve2", "reserve3", "reserve4", "reserve5", "create_by", "create_time",
				"last_modify_by", "last_modify_time" };
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), params);
	}
	
	/**
	 * 
	 * 描述：查询检索报表信息（查询往期报表数据）
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	public Object querySearch(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		String[] orgids = user.getCurSubOrgId().split(",");
		DecimalFormat df = new DecimalFormat("0.00");//格式化小数
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			int type = jsonObject.getInt("type");
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			
			if(orgids.length == 1){
	        	// 判断当前人员所在机构是否有子机构，如果没有，则为最基层机构
	        	// 最基层机构的数据需要从各个表中取得
	        	
	        	// 首先从表中查找该部门本段（季度或年度）是否已经上报了数据
	        	String querySql = "select bb.*,'0',d.caption from tb_rkyjhsy_baobiao2 bb , t_department d where bb.org_id=d.department_id and bb.org_id='" + user.getCurOrgId() + "' and bb.bb_year=" + year + " and bb.bb_quarter=" + quarter;
	        	List<Map<String, Object>> bbRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, querySql, null);
	        	// 判断是否有数据
	        	if(bbRecordList.size() > 0){
	        		Map<String, Object> bbMap = new HashMap<String, Object>();
	        		Map<String, Object> globleMap = bbRecordList.get(0);
	        		bbMap.put("Total", "1");
	        		bbMap.put("tbr_name", globleMap.get("tbr_name"));
	        		bbMap.put("tbr_tel", globleMap.get("tbr_tel"));
	        		bbMap.put("date_month", globleMap.get("date_month"));
	        		bbMap.put("date_day", globleMap.get("date_day"));
	        		bbMap.put("Shangbao", "0");
	        		
	        		bbMap.put("Rows", bbRecordList);
	        		bbMap.put("Sum", bbRecordList.get(0));
	        		
	        		JSONObject object = JSONObject.fromObject(bbMap);
					return object.toString();
	        	}else{
	        		return "{\"Total\":\"0\",\"Rows\":[]}";
	        	}
			}else{
	        	// 如果当前机构有子机构，那么则从统计报表中取出子机构的数据并加以统计
				String nvlSql = "";
				if("mysql".equals(user.getDbType())){
	        		nvlSql = "ifnull(bb.bb_id,0),ifnull(bb.date_month,0),ifnull(bb.date_day,0),ifnull(bb.tbr_name,0),ifnull(bb.tbr_tel,0),d.department_id,ifnull(bb.bb_type,0),ifnull(bb.bb_year,0),ifnull(bb.bb_quarter,0),ifnull(bb.e_zgrs_hj,0),ifnull(bb.e_zgrs_nan,0),ifnull(bb.e_zgrs_nv,0),ifnull(bb.e_hyzk_jhrs,0),ifnull(bb.e_hyzk_whrs,0),ifnull(bb.e_hyzk_whl,0),ifnull(bb.e_syqk_csyes,0),ifnull(bb.e_syqk_qz_jhnyt,0),ifnull(bb.e_syqk_qz_jhnet,0),ifnull(bb.e_syqk_qz_jhwcs,0),ifnull(bb.e_syqk_qz_wyrs,0),ifnull(bb.e_syqk_qz_wyl,0),ifnull(bb.e_syqk_qz_jhsyl,0),ifnull(bb.e_syqk_qz_hyrs,0),ifnull(bb.e_dsznqk_yingbanzheng,0),ifnull(bb.e_dsznqk_yibanzheng,0),ifnull(bb.e_dsznqk_bzl,0),ifnull(bb.e_dsznqk_xydszns,0),ifnull(bb.e_ssqk_rl,0),ifnull(bb.e_ssqk_sh,0),ifnull(bb.e_ssqk_jz,0),ifnull(bb.e_ssqk_yc_zq,0),ifnull(bb.e_ssqk_yc_dyf,0),ifnull(bb.e_blzsyzms,0),ifnull(bb.reserve1,0),ifnull(bb.reserve2,0),ifnull(bb.reserve3,0),ifnull(bb.reserve4,0),ifnull(bb.reserve5,0),ifnull(bb.create_by,0),ifnull(bb.create_time,0),ifnull(bb.last_modify_by,0),ifnull(bb.last_modify_time,0) ";
	        	}else{
	        		nvlSql = "nvl(bb.bb_id,0),nvl(bb.date_month,0),nvl(bb.date_day,0),nvl(bb.tbr_name,0),nvl(bb.tbr_tel,0),d.department_id,nvl(bb.bb_type,0),nvl(bb.bb_year,0),nvl(bb.bb_quarter,0),nvl(bb.e_zgrs_hj,0),nvl(bb.e_zgrs_nan,0),nvl(bb.e_zgrs_nv,0),nvl(bb.e_hyzk_jhrs,0),nvl(bb.e_hyzk_whrs,0),nvl(bb.e_hyzk_whl,0),nvl(bb.e_syqk_csyes,0),nvl(bb.e_syqk_qz_jhnyt,0),nvl(bb.e_syqk_qz_jhnet,0),nvl(bb.e_syqk_qz_jhwcs,0),nvl(bb.e_syqk_qz_wyrs,0),nvl(bb.e_syqk_qz_wyl,0),nvl(bb.e_syqk_qz_jhsyl,0),nvl(bb.e_syqk_qz_hyrs,0),nvl(bb.e_dsznqk_yingbanzheng,0),nvl(bb.e_dsznqk_yibanzheng,0),nvl(bb.e_dsznqk_bzl,0),nvl(bb.e_dsznqk_xydszns,0),nvl(bb.e_ssqk_rl,0),nvl(bb.e_ssqk_sh,0),nvl(bb.e_ssqk_jz,0),nvl(bb.e_ssqk_yc_zq,0),nvl(bb.e_ssqk_yc_dyf,0),nvl(bb.e_blzsyzms,0),nvl(bb.reserve1,0),nvl(bb.reserve2,0),nvl(bb.reserve3,0),nvl(bb.reserve4,0),nvl(bb.reserve5,0),nvl(bb.create_by,0),nvl(bb.create_time,0),nvl(bb.last_modify_by,0),nvl(bb.last_modify_time,0) ";
	        	}
	        	String subOrgIds = this.getSubIdsSql(user.getCurOrgId());
	        	
	        	StringBuffer querySql = new StringBuffer("select ").append(nvlSql).append(",(select count(*) from t_department where dparent_id=d.department_id), d.caption from t_department d left join tb_rkyjhsy_baobiao2 bb on (d.department_id=bb.org_id and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(") where d.department_id in(").append(subOrgIds).append(") order by d.ordernum ");
	        	List<Map<String, Object>> bbRecordList = AccessDataBaseUtil.query4ListMap(bb_fields, querySql.toString(), null);
	        	
	        	String curOrgRecordSql = "select bb.date_month,bb.date_day,bb.tbr_name,bb.tbr_tel from tb_rkyjhsy_baobiao2 bb where bb.org_id='" + user.getCurOrgId() + "' and bb.bb_year=" + year + " and bb.bb_quarter=" + quarter;
	        	List<Object[]> curOrgRecordList = AccessDataBaseUtil.query(curOrgRecordSql, null);
	        	
	        	//判断是否有数据
				if(bbRecordList != null && bbRecordList.size() > 0){
					//定义合计数据
		        	int sum_e_zgrs_hj = 0;
					int sum_e_zgrs_nan = 0;
					int sum_e_zgrs_nv = 0;
					int sum_e_hyzk_jhrs = 0;
					int sum_e_hyzk_whrs = 0;
					String sum_e_hyzk_whl = "100.00";
					int sum_e_syqk_csyes = 0;
					int sum_e_syqk_qz_jhnyt = 0;
					int sum_e_syqk_qz_jhnet = 0;
					int sum_e_syqk_qz_jhwcs = 0;
					int sum_e_syqk_qz_wyrs = 0;
					String sum_e_syqk_qz_wyl = "100.00";
					String sum_e_syqk_qz_jhsyl = "100.00";
					int sum_e_syqk_qz_hyrs = 0;
					int sum_e_dsznqk_yingbanzheng = 0;
					int sum_e_dsznqk_yibanzheng = 0;
					String sum_e_dsznqk_bzl = "100.00";
					int sum_e_dsznqk_xydszns = 0;
					int sum_e_ssqk_rl = 0;
					int sum_e_ssqk_sh = 0;
					int sum_e_ssqk_jz = 0;
					int sum_e_ssqk_yc_zq = 0;
					int sum_e_ssqk_yc_dyf = 0;
					int sum_e_blzsyzms = 0;
		        	
		        	// 循环所有子部门数据计算合计
		        	for(Map<String, Object> bbRecordMap : bbRecordList){
		        		sum_e_zgrs_hj += Integer.parseInt(bbRecordMap.get("e_zgrs_hj").toString());
		        		sum_e_zgrs_nan += Integer.parseInt(bbRecordMap.get("e_zgrs_nan").toString());
		        		sum_e_zgrs_nv += Integer.parseInt(bbRecordMap.get("e_zgrs_nv").toString());
		        		sum_e_hyzk_jhrs += Integer.parseInt(bbRecordMap.get("e_hyzk_jhrs").toString());
		        		sum_e_hyzk_whrs += Integer.parseInt(bbRecordMap.get("e_hyzk_whrs").toString());
		        		sum_e_syqk_csyes += Integer.parseInt(bbRecordMap.get("e_syqk_csyes").toString());
		        		sum_e_syqk_qz_jhnyt += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhnyt").toString());
		        		sum_e_syqk_qz_jhnet += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhnet").toString());
		        		sum_e_syqk_qz_jhwcs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_jhwcs").toString());
		        		sum_e_syqk_qz_wyrs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_wyrs").toString());
		        		sum_e_syqk_qz_hyrs += Integer.parseInt(bbRecordMap.get("e_syqk_qz_hyrs").toString());
		        		sum_e_dsznqk_yingbanzheng += Integer.parseInt(bbRecordMap.get("e_dsznqk_yingbanzheng").toString());
		        		sum_e_dsznqk_yibanzheng += Integer.parseInt(bbRecordMap.get("e_dsznqk_yibanzheng").toString());
		        		sum_e_dsznqk_xydszns += Integer.parseInt(bbRecordMap.get("e_dsznqk_xydszns").toString());
		        		sum_e_ssqk_rl += Integer.parseInt(bbRecordMap.get("e_ssqk_rl").toString());
		        		sum_e_ssqk_sh += Integer.parseInt(bbRecordMap.get("e_ssqk_sh").toString());
		        		sum_e_ssqk_jz += Integer.parseInt(bbRecordMap.get("e_ssqk_jz").toString());
		        		sum_e_ssqk_yc_zq += Integer.parseInt(bbRecordMap.get("e_ssqk_yc_zq").toString());
		        		sum_e_ssqk_yc_dyf += Integer.parseInt(bbRecordMap.get("e_ssqk_yc_dyf").toString());
		        		sum_e_blzsyzms += Integer.parseInt(bbRecordMap.get("e_blzsyzms").toString());
		        	}
					
					sum_e_hyzk_whl = sum_e_hyzk_jhrs == 0 ? df.format(0) : df.format((float)(sum_e_hyzk_whrs * 100)/sum_e_hyzk_jhrs);
					sum_e_syqk_qz_wyl = sum_e_syqk_qz_jhnyt == 0 ? df.format(0) : df.format((float)(sum_e_syqk_qz_wyrs * 100)/sum_e_syqk_qz_jhnyt);
					sum_e_dsznqk_bzl = sum_e_dsznqk_yingbanzheng == 0 ? df.format(0) : df.format((float)(sum_e_dsznqk_yibanzheng * 100)/sum_e_dsznqk_yingbanzheng);
					
					//定义合计Map
					Map<String, Object> sumMap = new HashMap<String, Object>();
					//向合计Map里装填数据
					sumMap.put("e_zgrs_hj", sum_e_zgrs_hj);
					sumMap.put("e_zgrs_nan",sum_e_zgrs_nan);
					sumMap.put("e_zgrs_nv",sum_e_zgrs_nv);
					sumMap.put("e_hyzk_jhrs",sum_e_hyzk_jhrs);
					sumMap.put("e_hyzk_whrs",sum_e_hyzk_whrs);
					sumMap.put("e_hyzk_whl",sum_e_hyzk_whl);
					sumMap.put("e_syqk_csyes",sum_e_syqk_csyes);
					sumMap.put("e_syqk_qz_jhnyt",sum_e_syqk_qz_jhnyt);
					sumMap.put("e_syqk_qz_jhnet",sum_e_syqk_qz_jhnet);
					sumMap.put("e_syqk_qz_jhwcs",sum_e_syqk_qz_jhwcs);
					sumMap.put("e_syqk_qz_wyrs",sum_e_syqk_qz_wyrs);
					sumMap.put("e_syqk_qz_wyl",sum_e_syqk_qz_wyl);
					sumMap.put("e_syqk_qz_jhsyl",sum_e_syqk_qz_jhsyl);
					sumMap.put("e_syqk_qz_hyrs",sum_e_syqk_qz_hyrs);
					sumMap.put("e_dsznqk_yingbanzheng",sum_e_dsznqk_yingbanzheng);
					sumMap.put("e_dsznqk_yibanzheng",sum_e_dsznqk_yibanzheng);
					sumMap.put("e_dsznqk_bzl",sum_e_dsznqk_bzl);
					sumMap.put("e_dsznqk_xydszns",sum_e_dsznqk_xydszns);
					sumMap.put("e_ssqk_rl",sum_e_ssqk_rl);
					sumMap.put("e_ssqk_sh",sum_e_ssqk_sh);
					sumMap.put("e_ssqk_jz",sum_e_ssqk_jz);
					sumMap.put("e_ssqk_yc_zq",sum_e_ssqk_yc_zq);
					sumMap.put("e_ssqk_yc_dyf",sum_e_ssqk_yc_dyf);
					sumMap.put("e_blzsyzms",sum_e_blzsyzms);
		        	
					
		        	Map<String, Object> bbMap = new HashMap<String, Object>();
	        		if(curOrgRecordList != null && curOrgRecordList.size() > 0){
	        			Object[] curOrgRecord = curOrgRecordList.get(0);
		        		bbMap.put("date_month", curOrgRecord[0].toString());
		        		bbMap.put("date_day", curOrgRecord[1].toString());
		        		bbMap.put("tbr_name", curOrgRecord[2].toString());
		        		bbMap.put("tbr_tel", curOrgRecord[3].toString());
		        		bbMap.put("Shangbao", "0");
	        		}else{
		        		bbMap.put("tbr_name", user.getUserrelName());
		        		bbMap.put("tbr_tel", "");
	        			bbMap.put("Shangbao", "1");
	        		}
	        		
	        		bbMap.put("Total", bbRecordList.size());
	        		bbMap.put("Rows", bbRecordList);
	        		bbMap.put("Sum", sumMap);
	        		
	        		JSONObject object = JSONObject.fromObject(bbMap);
					return object.toString();
				}else{
					return "{\"Total\":\"0\",\"Rows\":[]}";
				}
	        }
		}
		
		return "{\"Total\":\"0\",\"Rows\":[]}";
	}
	
	
	/**
	 * 
	 * 描述：下载报表
	 * @param jsonArray
	 * @return Object
	 * @throws Exception
	 * @CreateOn Sep 13, 2012 12:30:36 PM
	 * @author bai_xin
	 */
	public Object download(String jsonArray) throws Exception {
		//XML文件所在真实路径
		String url = this.getRequest().getSession().getServletContext().getRealPath("/jsp/app/xls/");
		//模板文件所在位置
		String model = url + "/model/rkyjhsy_2.xls";
		//装填好的Excel文件所在文职
		String file = url + "/file/rkyjhsy_2.xls";
		//文件下载路径（最后返回的路径）
		String rfile = "jsp/app/xls/file/rkyjhsy_2.xls";
		//获取页面传入的logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		//判断如果有值则继续进行，没有则返回相应错误提示信息
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			//取出传入的所有部门的数据
			JSONArray allData = jsonObject.getJSONArray("data");
			//报表类型 1：季度 2：年度
			int type = jsonObject.getInt("type");
			//报表年份
			int year = jsonObject.getInt("year");
			//报表季度（如果为年报，则quarter=0）
			int quarter = jsonObject.getInt("quarter");
			//填表日期（表格右上角的日期字段）
			String dateMonth = jsonObject.getString("date_month");
			//填表日期（表格右上角的日期字段）
			String dateDay = jsonObject.getString("date_day");
			//填表人名称
			String tbrName = jsonObject.getString("tbr_name");
			//联系电话
			String tbrTel = jsonObject.getString("tbr_tel");
			
			//通过模板文件复制一份文件
			File ffrom = new File(model);
			File fto = new File(file);
			Common.copyFile(ffrom, fto);
			
			//设置非部门数据内容，如：日期、填表人、标题等
			String tbr = "填表人：" + tbrName + "                                                          联系电话：" + tbrTel + "                                                                  ";
			String date = year + "年" + dateMonth + "月" + dateDay + "日";
			String title = "第一汽车集团公司人口与计划生育" + year + "年" + ((type == 1) ? "第" + quarter + "季度" : "") + "统计表（二）";
			//判断是否有部门数据
			if(allData.size() > 0){
				//打开刚刚生成好的Excel文件并生成相应的POI类对象
				POIFSFileSystem filein = new POIFSFileSystem(new FileInputStream(fto));
				HSSFWorkbook book = new HSSFWorkbook(filein);
				HSSFSheet sheet = book.getSheet("人口与计划生育季统计表（二）");
				//数据起始位置行号（前5行为表头，所以数据从第5行开始填入excel）
				int startRow = 5;
				//定义POI的Excel行对象
				HSSFRow row = null;
				HSSFCellStyle borderStyle = book.createCellStyle();
				borderStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				borderStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				borderStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				borderStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				//设置表格标题风格和内容
				row = sheet.createRow(0);
				row.setHeight((short) 900);
				HSSFCell cell0_0 = row.createCell(0);
				HSSFCellStyle cstyle0 = book.createCellStyle();
				HSSFFont titleFont = book.createFont();
				titleFont.setFontName("黑体");
				titleFont.setFontHeightInPoints((short) 16);//设置字体大小
				cstyle0.setFont(titleFont);
				cstyle0.setAlignment(CellStyle.ALIGN_CENTER);
				cstyle0.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				cstyle0.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				cstyle0.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				cstyle0.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				cstyle0.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				cell0_0.setCellStyle(cstyle0);
				cell0_0.setCellValue(new HSSFRichTextString(title));
				for(int i = 1 ; i < 26 ; i++){
					HSSFCell tempCell = row.createCell(i);
					tempCell.setCellStyle(borderStyle);
				}
				
				//设置日期风格和内容
				row = sheet.createRow(1);
				row.setHeight((short) 500);
				HSSFCell cell1_0 = row.createCell(0);
				HSSFCellStyle cstyle1 = book.createCellStyle();
				cstyle1.setAlignment(CellStyle.ALIGN_RIGHT);
				cstyle1.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				cstyle0.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				cstyle0.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				cstyle0.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				cstyle0.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				cell1_0.setCellStyle(cstyle1);
				cell1_0.setCellValue(date);
				for(int i = 1 ; i < 26 ; i++){
					HSSFCell tempCell = row.createCell(i);
					tempCell.setCellStyle(borderStyle);
				}
				
				HSSFCellStyle doubleStyle = book.createCellStyle();
				doubleStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
				doubleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				doubleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				doubleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				doubleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				//循环填入每个部门的数据
				for (int i = 0; i < allData.size(); i++) {
					row = sheet.createRow(i + startRow);
					row.setHeight((short) 400);
					//序号
					HSSFCell cell = row.createCell(0);
					HSSFCellStyle cstyle = book.createCellStyle();
					cstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
					cstyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
					cstyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
					cstyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
					cell.setCellStyle(cstyle);
					cell.setCellValue((i+1) + "");
					//取出每一行数据
					JSONObject rowData = allData.getJSONObject(i);
					//循环每个数据列，并装填到excel单元格中
					for(int j = 0; j < rowData.size(); j++) {
						cell = row.createCell(j + 1);
						cell.setCellStyle(cstyle);
						// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
						if(j == 0){
							cell.setCellValue(rowData.getString("" + (j + 1)));
						}else if(j == 6 || j == 12 || j == 13 || j == 17){
							cell.setCellValue(rowData.getDouble("" + (j + 1)));
				            cell.setCellStyle(doubleStyle);
						}else{
							cell.setCellValue(rowData.getInt("" + (j + 1)));
						}
					}
				}
				//填入填表人和联系电话等数据
				row = sheet.createRow(startRow + allData.size());
				row.setHeight((short) 500);
				HSSFCell tbrCell = row.createCell(0);
				HSSFCellStyle cstyle2 = book.createCellStyle();
				cstyle2.setAlignment(CellStyle.ALIGN_LEFT);
				cstyle2.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				cstyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
				cstyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
				cstyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
				cstyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
				tbrCell.setCellStyle(cstyle2);
				tbrCell.setCellValue(tbr);
				for(int i = 1 ; i < 26 ; i++){
					HSSFCell tempCell = row.createCell(i);
					tempCell.setCellStyle(borderStyle);
				}
				
				//输出文件并关闭流
				FileOutputStream fileOut = new FileOutputStream(file);
				book.write(fileOut);
				fileOut.close();
			} else {
				return "{\"result\":false,\"resultmessage\":\"\",\"resultrecord\":\"报表中没有记录\"}";
			}
		}
		//将文件的下载地址路径返回
		return "\"" + rfile + "\"";
	}
	
	/**
	 * 回退报表
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object sendback(Object jsonArray) throws Exception {
		//获取当前用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		//判断如果有值则继续进行，没有则返回相应错误提示信息
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			//取出传入的所有要退回报表的部门ID
			String orgIds = jsonObject.getString("org_ids");
			String[] orgArray = orgIds.split(",");
			//报表类型 1：季度 2：年度
			int type = jsonObject.getInt("type");
			//报表年份
			int year = jsonObject.getInt("year");
			//报表季度（如果为年报，则quarter=0）
			int quarter = jsonObject.getInt("quarter");
			
			//首先验证对应的报表一是否已经上报，如果已经上报则返回错误提示：“请先联系上级单位退回本机构报表一，再进行下级机构的报表退回操作！”
			/*
			String orgId = jsonObject.getString("org_id");
			StringBuffer baobiao1Sql = new StringBuffer("select count(*) from tb_rkyjhsy_baobiao1 where bb_type='").append(type).append("' and bb_year=").append(year).append(" and bb_quarter=").append(quarter).append(" and org_id='").append(orgId).append("'");
			int bb1 = AccessDataBaseUtil.queryForInt(baobiao1Sql.toString(), null);
			if(bb1 > 0){
				return "{\"status\":\"failure\",\"message\":\"无法退回，报表一已经上报，请先联系上级单位退回本机构报表一，再进行下级机构的报表退回操作！\"}";
			}
			*/
			if(orgArray != null && orgArray.length > 0){
				List<String> sqlList = new ArrayList<String>();
				for(int i = 0 ; i < orgArray.length ; i++){
					sqlList.add("update tbe_neonate_main set reserve5='' where reserve5='" + orgArray[i] + "-" + year + "-" + quarter + "'");
					sqlList.add("update tbc_surgery_record set reserve5='' where reserve5='" + orgArray[i] + "-" + year + "-" + quarter + "'");
					sqlList.add("update tbe_faa_birth_record set reserve5='' where reserve5='" + orgArray[i] + "-" + year + "-" + quarter + "'");
					sqlList.add("update tb_employee set reserve5='1' where reserve5='" + orgArray[i] + "-" + year + "-" + quarter + "'");
					sqlList.add("delete from tb_rkyjhsy_baobiao1 where org_id='" + orgArray[i] + "' and bb_type=" + type + " and bb_year=" + year + " and bb_quarter=" + quarter);
					sqlList.add("delete from tb_rkyjhsy_baobiao2 where org_id='" + orgArray[i] + "' and bb_type=" + type + " and bb_year=" + year + " and bb_quarter=" + quarter);
					if(type == 1 && quarter == 4){
						sqlList.add("delete from tb_rkyjhsy_baobiao1 where org_id='" + orgArray[i] + "' and bb_type=2 and bb_year=" + year + " and bb_quarter=0");
						sqlList.add("delete from tb_rkyjhsy_baobiao2 where org_id='" + orgArray[i] + "' and bb_type=2 and bb_year=" + year + " and bb_quarter=0");
					}
				}
				// 批量回退所有数据
				AccessDataBaseUtil.executeSQLUpdate(sqlList);

				// 提醒退回报表的部门
				StringBuffer notice = new StringBuffer("注意，您上报的").append(year).append("年");
				if(type == 1){
					notice.append("第").append(quarter).append("季度");
					if(quarter == 4){
						notice.append("和年度");
					}
				}
				notice.append("的人口与计划生育统计表已经退回，请重新上报！");
				this.sendNotice(user, orgArray, notice.toString(), "0", type + "-" + year + "-" + quarter, 0);
			}else{
				return "{\"status\":\"failure\",\"message\":\"退回失败， 请刷新页面重试，或联系技术支持人员！\"}";
			}
		}else{
			return "{\"status\":\"failure\",\"message\":\"退回失败， 请刷新页面重试，或联系技术支持人员！\"}";
		}
		return "{\"status\":\"success\",\"message\":\"退回成功！\"}";
	}
	
	/**
	 * 提醒未上报部门
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object alert(Object jsonArray) throws Exception {
		//获取当前用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//获取logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			int type = jsonObject.getInt("type");
			String announcement = URLDecoder.decode(URLDecoder.decode(jsonObject.getString("announcement"),"UTF-8"),"UTF-8");
			String ids = jsonObject.getString("ids");
			String[] unsubmitIds = ids.split(",");
			int atime = jsonObject.getInt("atime");
			int ntime = jsonObject.getInt("ntime");
			
			StringBuffer notice = new StringBuffer("一汽计生办提醒您，尽快上报").append(year).append("年");
			if(type == 1){
				notice.append("第").append(quarter).append("季度人口与计划生育统计表");
			}else{
				notice.append("人口与计划生育统计表");
			}
			if(ids != null && unsubmitIds.length > 0){
				String tmaid = this.sendAnnouncement(user, announcement, type + "-" + year + "-" + quarter, 2, atime);
				this.sendNotice(user, unsubmitIds, notice.toString(), tmaid, type + "-" + year + "-" + quarter, ntime);
			}else{
				return "{\"status\":\"failure\",\"message\":\"通知和公告发送失败， 请刷新页面重试，或联系技术支持人员！\"}";
			}
			return "{\"status\":\"success\",\"message\":\"通知和公告发送成功！\"}";
		}else{
			return "{\"status\":\"failure\",\"message\":\"通知和公告发送失败， 请刷新页面重试，或联系技术支持人员！\"}";
		}
	}
	

	/**
	 * 一键提取未上报子机构数据并上报
	 * @param jsonArray
	 * @return
	 * @throws Exception
	 */
	public Object pickup(Object jsonArray) throws Exception {
		//获取当前用户对象
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		//获取logicdata
		JSONArray array = JSONArray.fromObject(jsonArray);
		if(array.size() > 0) {
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			int year = jsonObject.getInt("year");
			int quarter = jsonObject.getInt("quarter");
			int type = jsonObject.getInt("type");
			//String ids = jsonObject.getString("ids");
			//String[] unsubmitIds = ids.split(",");
			//String allSubIds = user.getCurSubOrgIds(user.getCurOrgId());
			//allSubIds = allSubIds.substring(34);
			String allSubIds = this.getAllSubIdsSql(user.getCurOrgId());
			if(allSubIds != null && !"".equals(allSubIds)){
				List<Object> ids1 = this.getLeafUnsubmitBaobiao1(allSubIds, type, year, quarter);
				List<Object> ids2 = this.getLeafUnsubmitBaobiao2(allSubIds, type, year, quarter);
				List<Object> ids3_3 = this.getUnleafUnsubmitBaobiao1(allSubIds, 3, type, year, quarter);
				List<Object> ids3_2 = this.getUnleafUnsubmitBaobiao1(allSubIds, 2, type, year, quarter);
				List<Object> ids3_1 = this.getUnleafUnsubmitBaobiao1(allSubIds, 1, type, year, quarter);
				List<Object> ids4_3 = this.getUnleafUnsubmitBaobiao2(allSubIds, 3, type, year, quarter);
				List<Object> ids4_2 = this.getUnleafUnsubmitBaobiao2(allSubIds, 2, type, year, quarter);
				List<Object> ids4_1 = this.getUnleafUnsubmitBaobiao2(allSubIds, 1, type, year, quarter);

				this.submitLeafBaobiao1(ids1, type, year, quarter);
				this.submitLeafBaobiao2(ids2, type, year, quarter);
				this.submitUnleafBaobiao1(ids3_3, type, year, quarter);
				this.submitUnleafBaobiao1(ids3_2, type, year, quarter);
				this.submitUnleafBaobiao1(ids3_1, type, year, quarter);
				this.submitUnleafBaobiao2(ids4_3, type, year, quarter);
				this.submitUnleafBaobiao2(ids4_2, type, year, quarter);
				this.submitUnleafBaobiao2(ids4_1, type, year, quarter);
				
				if(type == 1 && quarter == 4){
					List<Object> ids5 = this.getUnsubmitNianBaobiao1(allSubIds, 2, year, 0);
					List<Object> ids6 = this.getUnsubmitNianBaobiao2(allSubIds, 2, year, 0);
					this.submitNianBaobiao1(ids5, 2, year, 0);
					this.submitNianBaobiao2(ids6, 2, year, 0);
				}
			}else{
				return "{\"status\":\"failure\",\"message\":\"一键上报失败， 请刷新页面重试，或联系技术支持人员！\"}";
			}
			return "{\"status\":\"success\",\"message\":\"一键上报成功！\"}";
		}else{
			return "{\"status\":\"failure\",\"message\":\"一键上报失败， 请刷新页面重试，或联系技术支持人员！\"}";
		}
	}
	
	/**
	 * 
	 * 描述：根据给定参数获取日期判断SQL
	 * @param 
	 * @return
	 * @throws Exception
	 * @CreateOn Sep 20, 2012 4:59:29 PM
	 * @author bai_xin
	 */
	private String getDateRangeSql(String dbType, String dbColumn, String dateBegin, String dateEnd, int compareType){
		// compareType:1-在日期范围内包含边界 ；2-在日期范围外包含边界；3-在日期范围内不包含边界；4-在日期范围外不包含边界
		String result = null;
		if("mysql".equals(dbType)){
			switch(compareType){
				case 1: result = "DATE_FORMAT('" + dateBegin + "', '%Y-%m-%d') <= DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d') and DATE_FORMAT('" + dateEnd + "', '%Y-%m-%d') >= DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d')";break;
				case 2: result = "DATE_FORMAT('" + dateBegin + "', '%Y-%m-%d') >= DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d') and DATE_FORMAT('" + dateEnd + "', '%Y-%m-%d') <= DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d')";break;
				case 3: result = "DATE_FORMAT('" + dateBegin + "', '%Y-%m-%d') < DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d') and DATE_FORMAT('" + dateEnd + "', '%Y-%m-%d') > DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d')";break;
				case 4: result = "DATE_FORMAT('" + dateBegin + "', '%Y-%m-%d') > DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d') and DATE_FORMAT('" + dateEnd + "', '%Y-%m-%d') < DATE_FORMAT(" + dbColumn + ", '%Y-%m-%d')";break;
			}
		}else if("oracle".equals(dbType)){
			switch(compareType){
				case 1: result = "TO_DATE('" + dateBegin + "', 'yyyy-mm-dd') <= TO_DATE(" + dbColumn + ", 'yyyy-mm-dd') and TO_DATE('" + dateEnd + "', 'yyyy-mm-dd') >= TO_DATE(" + dbColumn + ", 'yyyy-mm-dd')";break;
				case 2: result = "TO_DATE('" + dateBegin + "', 'yyyy-mm-dd') >= TO_DATE(" + dbColumn + ", 'yyyy-mm-dd') and TO_DATE('" + dateEnd + "', 'yyyy-mm-dd') <= TO_DATE(" + dbColumn + ", 'yyyy-mm-dd')";break;
				case 3: result = "TO_DATE('" + dateBegin + "', 'yyyy-mm-dd') < TO_DATE(" + dbColumn + ", 'yyyy-mm-dd') and TO_DATE('" + dateEnd + "', 'yyyy-mm-dd') > TO_DATE(" + dbColumn + ", 'yyyy-mm-dd')";break;
				case 4: result = "TO_DATE('" + dateBegin + "', 'yyyy-mm-dd') > TO_DATE(" + dbColumn + ", 'yyyy-mm-dd') and TO_DATE('" + dateEnd + "', 'yyyy-mm-dd') < TO_DATE(" + dbColumn + ", 'yyyy-mm-dd')";break;
			}
		}
		
		return result;
	}
	
	/**
	 * 
	 * 描述：获取指定机构的下一级子机构ID SQL字符串，格式为'id1','id2','id3','id4'
	 * @param 
	 * parentId 父机构ID
	 * @return
	 * String 字符串
	 * @throws Exception
	 * @CreateOn Oct 12, 2012 10:36:44 AM
	 * @author bai_xin
	 */
	private String getSubIdsSql(String parentId) throws Exception{
		String subOrgIdsSql = "select d.department_id from t_department d where d.dparent_id=? order by d.ordernum";
    	List<Object> subOrgIdsList = AccessDataBaseUtil.query4List(subOrgIdsSql, new String[]{parentId});
		StringBuffer sb = new StringBuffer();
		if(subOrgIdsList == null || subOrgIdsList.size() == 0) return null;
		for(int i = 0 ; i < subOrgIdsList.size() ; i++){
			sb.append("'");
			sb.append(subOrgIdsList.get(i));
			sb.append("'");
			if(i != subOrgIdsList.size() - 1) sb.append(",");
		}
		return sb.toString();
	}
	
	/**
	 * 
	 * 描述：根据指定的父机构ID查询下级子机构的ID并拼接Sql字符串（查询下面所有机构）
	 * @param parentId
	 * 父级机构的ID
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	private String getAllSubIdsSql(String parentId) throws Exception{
		String result = this.getAllSubIdsSqlLoop(parentId);
		if(result == null || "".equals(result)){
			return "";
		}
		return result.substring(0, result.length() - 1);
	}
	
	/**
	 * 
	 * 注意：此方法为递归循环数据处理，请使用getAllSubIdsSql方法
	 * @param parentId
	 * 父级机构的ID
	 * @return Object
	 * @throws Exception
	 * @CreateOn Oct 22, 2012 16:03:36 PM
	 * @author bai_xin
	 */
	private String getAllSubIdsSqlLoop(String parentId) throws Exception{
		String subOrgIdsSql = "select d.department_id from t_department d where d.dparent_id=? order by d.ordernum";
    	List<Object> subOrgIdsList = AccessDataBaseUtil.query4List(subOrgIdsSql, new String[]{parentId});
		StringBuffer sb = new StringBuffer();
		if(subOrgIdsList == null || subOrgIdsList.size() == 0) return "";
		for(int i = 0 ; i < subOrgIdsList.size() ; i++){
			sb.append("'");
			sb.append(subOrgIdsList.get(i));
			sb.append("',");
			sb.append(getAllSubIdsSqlLoop(subOrgIdsList.get(i).toString()));
		}
		return sb.toString();
	}
	
	/**
	 * 从指定的ID字符串中，获取未上报指定年度和季度表一的叶子（最低级）机构部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getLeafUnsubmitBaobiao1(String ids, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND NOT EXISTS ( SELECT td.department_id FROM t_department td WHERE td.dparent_id = d.department_id ) AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao1 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id )");
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}
	
	/**
	 * 从指定的ID字符串中，获取未上报指定年度和季度表二的叶子（最低级）机构部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getLeafUnsubmitBaobiao2(String ids, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND NOT EXISTS ( SELECT td.department_id FROM t_department td WHERE td.dparent_id = d.department_id ) AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao2 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id )");
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}
	
	/**
	 * 从指定的ID字符串中，获取未上报指定年度和季度表一的非叶子（有子机构）机构部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param levels
	 * 部门级别（对应于部门表levels字段）
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getUnleafUnsubmitBaobiao1(String ids, int levels, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND EXISTS ( SELECT td.department_id FROM t_department td WHERE td.dparent_id = d.department_id ) AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao1 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id ) and d.levels=").append(levels);
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}
	
	/**
	 * 从指定的ID字符串中，获取未上报指定年度和季度表二的非叶子（有子机构）机构部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param levels
	 * 部门级别（对应于部门表levels字段）
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getUnleafUnsubmitBaobiao2(String ids, int levels, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND EXISTS ( SELECT td.department_id FROM t_department td WHERE td.dparent_id = d.department_id ) AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao2 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id ) and d.levels=").append(levels);
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}
	
	/**
	 * 从指定的ID字符串中，找出未上报年度报表一的部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度（年报，这里应该传入0）
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getUnsubmitNianBaobiao1(String ids, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao1 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id )");
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}
	
	/**
	 * 从指定的ID字符串中，找出未上报年度报表二的部门ID
	 * @param ids
	 * 全部ID字符串，格式为'id1','id2','id3','id4'
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度（年报，这里应该传入0）
	 * @return List<Object>
	 * @throws Exception
	 */
	private List<Object> getUnsubmitNianBaobiao2(String ids, int type, int year, int quarter) throws Exception{
		StringBuffer sql = new StringBuffer("SELECT d.department_id FROM t_department d WHERE d.department_id IN (").append(ids).append(") AND NOT EXISTS ( SELECT bb.bb_id FROM tb_rkyjhsy_baobiao2 bb WHERE bb.bb_type=").append(type).append(" and bb.bb_year=").append(year).append(" and bb.bb_quarter=").append(quarter).append(" and bb.org_id = d.department_id )");
    	return AccessDataBaseUtil.query4List(sql.toString(), null);
	}

	/**
	 * 上报叶子（最低级）机构的报表一数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitLeafBaobiao1(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			
			// 计算女性育龄SQL
			String childbearingAgeRangeSql = this.getDateRangeSql(user.getDbType(), "emp.tbe_birthday", FormatUtil.getDateBeforeYear(49), FormatUtil.getDateBeforeYear(20), 1);
			// 计算男性育龄SQL
			String maleChildbearingAgeRangeSql = "(fb.tbfi_spouse_birthday is null or fb.tbfi_spouse_birthday='' or (" + this.getDateRangeSql(user.getDbType(), "fb.tbfi_spouse_birthday", FormatUtil.getDateBeforeYear(49), FormatUtil.getDateBeforeYear(20), 1) + "))";
			// 排除不计算的人员
			String notCountEmployeeSql = " emp.tbe_employment_forms!='31' and emp.tbe_status!='102-3' and emp.tbe_status!='102-4' and emp.tbe_status!='102-5' ";
			// 已婚无生育条件SQL
			String notHaveBirthConSql = "((emp.tbe_is_barrenness='1' and emp.the_is_menopause='0' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A323') or (emp.tbe_is_barrenness='0' and emp.the_is_menopause='1' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A323') or (emp.tbe_is_barrenness='1' and emp.the_is_menopause='1' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A323') or (emp.tbe_is_barrenness='0' and emp.the_is_menopause='0' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A323') or (emp.tbe_is_barrenness='1' and emp.the_is_menopause='0' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A322') or (emp.tbe_is_barrenness='0' and emp.the_is_menopause='0' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A322') or (emp.tbe_is_barrenness='1' and emp.the_is_menopause='1' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A322') or (emp.tbe_is_barrenness='0' and emp.the_is_menopause='0' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A322') or (emp.tbe_is_barrenness='1' and emp.the_is_menopause='0' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320') or (emp.tbe_is_barrenness='0' and emp.the_is_menopause='1' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320') or (emp.tbe_is_barrenness='1' and emp.the_is_menopause='1' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320'))";
			// 已婚有生育条件SQL
			String haveBirthConSql = "(emp.tbe_is_barrenness='0' or emp.tbe_is_barrenness is null) and (emp.the_is_menopause='0' or emp.the_is_menopause is null) and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A322' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A323' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320'";
			// 不计算怀孕或等孕的已落实节育措施女性员工
			String notCountYlsSql = "not exists(select * from tbe_pregnancy_record tbepr,tb_family_base fb where fb.tbe_id=emp.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbepr.tbepr_option!='2')";
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			for(Object id : ids){
				String curOrgId = (String) id;
				StringBuffer sqlsb = new StringBuffer();
		       	// 职工人数 合计
		       	sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and ").append(notCountEmployeeSql).append(" union all ");
		       	// 职工人数 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='男' and ").append(notCountEmployeeSql).append(" union all "); 
				// 职工人数 女
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='女' and ").append(notCountEmployeeSql).append(" union all ");
				// 职工人数 已婚 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='男' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320' and ").append(notCountEmployeeSql).append(" union all ");
				// 职工人数 已婚 女
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='女' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320' and ").append(notCountEmployeeSql).append(" union all ");
				// 职工人数 未婚 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='男' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A320' and ").append(notCountEmployeeSql).append(" union all ");
				// 职工人数 未婚 女
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='女' and emp.tbe_marital_status='8A90F0D0176C4961B322BEB26703A320' and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 合计
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320' and ((emp.tbe_sex='女' and " ).append( childbearingAgeRangeSql ).append( ") or (emp.tbe_sex='男' and ").append( maleChildbearingAgeRangeSql ).append(" )) and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='男' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320' and ").append(maleChildbearingAgeRangeSql).append(" and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 女
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='女' and emp.tbe_marital_status!='8A90F0D0176C4961B322BEB26703A320' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 无生育条件 合计 是否不孕：emp.tbe_is_barrenness 是否绝经：emp.the_is_menopause
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and ").append(notHaveBirthConSql).append(" and ((emp.tbe_sex='女' and " ).append( childbearingAgeRangeSql ).append( ") or (emp.tbe_sex='男' and ").append( maleChildbearingAgeRangeSql ).append(" )) and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 无生育条件 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='男' and ").append(notHaveBirthConSql).append(" and " ).append( maleChildbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 无生育条件 女 
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append( "' and emp.tbe_sex='女' and ").append(notHaveBirthConSql).append(" and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 有生育条件 合计
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and ").append(haveBirthConSql).append(" and ((emp.tbe_sex='女' and " ).append( childbearingAgeRangeSql ).append( ") or (emp.tbe_sex='男' and ").append( maleChildbearingAgeRangeSql ).append(" )) and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 有生育条件 男
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where fb.tbe_id=emp.tbe_id and emp.tbe_sex='男' and emp.org_id='" ).append( curOrgId ).append( "' and ").append(haveBirthConSql).append(" and " ).append( maleChildbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 已婚育龄 有生育条件 女
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and emp.org_id='" ).append( curOrgId ).append( "' and ").append(haveBirthConSql).append(" and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 合计 已婚有生育条件（女）-怀孕人数（女）-等孕人数（女）
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 小计
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 结扎
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-1' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 上环
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-2' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 皮埋
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-3' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 工具
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-4' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 药膜
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-5' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 服药
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-6' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 有生育条件已婚育龄妇女应落实节育措施人数 已落实节育措施人数 其他
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.tbe_sex='女' and ").append(notCountYlsSql).append(" and emp.org_id='").append(curOrgId).append("' and ").append(haveBirthConSql).append(" and emp.tbe_birth_control_measures='104-7' and " ).append( childbearingAgeRangeSql ).append( " and ").append(notCountEmployeeSql).append(" union all ");
				// 未落实节育措施人数
				sqlsb.append( "select 0 from dual union all ");
				// 节育措施落实率
				sqlsb.append( "select '100' from dual union all ");
				// 怀孕人数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr where fb.tbe_id=emp.tbe_id and tbepr.tbfi_id=fb.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbepr.tbepr_option='0' and " ).append(notCountEmployeeSql).append(" union all ");
				// 等孕人数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr where fb.tbe_id=emp.tbe_id and tbepr.tbfi_id=fb.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbepr.tbepr_option='1' and " ).append(notCountEmployeeSql).append(" union all ");
				// 本季出生
				//sqlsb.append( "select count(tbenm.tbenm_id) from tbe_neonate_main tbenm where tbenm.org_id='" ).append( curOrgId ).append( "' and " ).append( this.getDateRangeSql("mysql", "tbenm.tbenm_birth_time", dateBegin, dateEnd, 1));
				if("mysql".equals(user.getDbType())){
					sqlsb.append( "select ").append(" ifnull(sum(tbenm.tbenm_gravida_num),0) ").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.reserve5='' and ").append(notCountEmployeeSql);
				}else{
					sqlsb.append( "select ").append(" nvl(sum(tbenm.tbenm_gravida_num),0) ").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.reserve5='' and ").append(notCountEmployeeSql);
				}
				
				List<Object> list = AccessDataBaseUtil.query4List(sqlsb.toString(), null);
				
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao1 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append(curOrgId).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0      1     2    3           4          5            6           7           8          9         
				e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,
				10              11             12            13              14             15            16             
				e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,
				17                 18                19               20               21               22               
				e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,
				23               24               25            26            27     28     29                 
				e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs
				*/
				insertSql.append(list.get(0).toString()).append(",");
				insertSql.append(list.get(1).toString()).append(",");
				insertSql.append(list.get(2).toString()).append(",");
				insertSql.append(list.get(3).toString()).append(",");
				insertSql.append(list.get(4).toString()).append(",");
				insertSql.append(list.get(5).toString()).append(",");
				insertSql.append(list.get(6).toString()).append(",");
				insertSql.append(list.get(7).toString()).append(",");
				insertSql.append(list.get(8).toString()).append(",");
				insertSql.append(list.get(9).toString()).append(",");
				//无生育合计
				insertSql.append(Integer.parseInt(list.get(11).toString()) + Integer.parseInt(list.get(12).toString())).append(",");
				insertSql.append(list.get(11).toString()).append(",");
				insertSql.append(list.get(12).toString()).append(",");
				insertSql.append(list.get(13).toString()).append(",");
				insertSql.append(list.get(14).toString()).append(",");
				insertSql.append(list.get(15).toString()).append(",");
				// 应落实节育措施人数
				int yls = Integer.parseInt(list.get(16).toString());
				insertSql.append(yls).append(",");
				// 已落实节育措施人数
				int ylsrs = Integer.parseInt(list.get(18).toString()) + 
							Integer.parseInt(list.get(19).toString()) + 
							Integer.parseInt(list.get(20).toString()) + 
							Integer.parseInt(list.get(21).toString()) + 
							Integer.parseInt(list.get(22).toString()) + 
							Integer.parseInt(list.get(23).toString()) +
							Integer.parseInt(list.get(24).toString());
				insertSql.append(ylsrs).append(",");
				insertSql.append(list.get(18).toString()).append(",");
				insertSql.append(list.get(19).toString()).append(",");
				insertSql.append(list.get(20).toString()).append(",");
				insertSql.append(list.get(21).toString()).append(",");
				insertSql.append(list.get(22).toString()).append(",");
				insertSql.append(list.get(23).toString()).append(",");
				insertSql.append(list.get(24).toString()).append(",");
				insertSql.append(list.get(25).toString()).append(",");
				// 节育措施落实率
				insertSql.append(yls == 0 ? df.format(0.00) : df.format((float)(ylsrs * 100)/yls)).append(",");
				insertSql.append(list.get(27).toString()).append(",");
				insertSql.append(list.get(28).toString()).append(",");
				insertSql.append(list.get(29).toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
			}
			
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 上报叶子（最低级）机构的报表二数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitLeafBaobiao2(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			// 排除不计算的人员
			String notCountEmployeeSql = " emp.tbe_employment_forms!='31' and emp.tbe_status!='102-3' and emp.tbe_status!='102-4' and emp.tbe_status!='102-5' ";
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			for(Object id : ids){
				String curOrgId = (String) id;
	       		String nvlFunction = "";
	        	if("mysql".equals(user.getDbType())){
	        		nvlFunction = "ifnull(";
	        	}else{
	        		nvlFunction = "nvl(";
	        	}
	       		
		       	StringBuffer sqlsb = new StringBuffer();
		       	//1 职工人数 合计
		       	sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and ").append(notCountEmployeeSql).append(" union all ");
		       	//2 职工人数 男职工
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='男' and ").append(notCountEmployeeSql).append(" union all "); 
				//3 职工人数 女职工
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.tbe_sex='女' and ").append(notCountEmployeeSql).append(" union all ");
				//4 婚姻状况 结婚人数 结婚时间：emp.tbe_married_time
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.reserve5='1' and ").append(notCountEmployeeSql).append(" union all ");
				//5 婚姻状况 晚婚人数 是否晚婚：emp.tbe_is_late_marriage
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp where emp.org_id='" ).append( curOrgId ).append("' and emp.reserve5='1' and emp.tbe_is_late_marriage='1' and ").append(notCountEmployeeSql).append(" union all ");
				//6 婚姻状况 晚婚率 （晚婚人数/结婚人数）
				sqlsb.append( "select 0 from dual union all ");
				
				//7 生育情况 出生婴儿数
				sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
				//8 生育情况 其中 计划内一胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
				sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='0' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
				//9 生育情况 其中 计划内二胎 计划：tbenm.tbenm_plan_status 胎数：tbenm.tbenm_gravida_times
				sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='1' and tbenm.reserve5='' and ").append(notCountEmployeeSql).append(" union all ");
				//10 生育情况 其中 计划外出生 计划：tbenm.tbenm_plan_status 由于不允许有计划外出生，所以这里默认为0 
				sqlsb.append( "select 0 from dual union all ");
				//11 生育情况 其中 晚育人数 是否晚婚+晚育tbenm.reserve4
				//sqlsb.append( "select count(tbenm.tbenm_id) from tbe_neonate_main tbenm where tbenm.reserve4='1' and tbenm.org_id='" ).append( curOrgId ).append( "' union all ");
				sqlsb.append( "select ").append(nvlFunction).append("sum(tbenm.tbenm_gravida_num),0)").append(" from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr , tbe_neonate_main tbenm where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and tbenm.tbepr_id=tbepr.tbepr_id and emp.org_id='" ).append( curOrgId ).append( "' and tbenm.tbenm_plan_status='0' and tbenm.reserve5='' and tbenm.reserve4='1' and ").append(notCountEmployeeSql).append(" union all ");
				//12 生育情况 其中 晚育率（这里先取生育人数，后面通过晚育人数/计划内一胎）
				sqlsb.append( "select 0 from dual union all ");
				//13 生育情况 其中 计划生育率(计划内一胎 + 计划内二胎)/出生婴儿数 由于不允许有计划外出生，所以这里默认为100.00
				sqlsb.append( "select 100 from dual union all ");
				//14 生育情况 其中 怀孕人数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_pregnancy_record tbepr where emp.tbe_id=fb.tbe_id and tbepr.tbfi_id=fb.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbepr.tbepr_option='0' and ").append(notCountEmployeeSql).append(" union all ");

				//15 独生子女情况 应办证职工数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and fb.tbfi_children_num=1 and ").append(notCountEmployeeSql).append(" union all ");
				//16 独生子女情况 已办证职工数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_only_child_record tbeocr where emp.tbe_id=fb.tbe_id and fb.tbfi_id=tbeocr.tbfi_id and emp.org_id='" ).append( curOrgId ).append( "' and tbeocr.reserve2!='1' and ").append(notCountEmployeeSql).append(" union all ");
				//17 独生子女情况 办证率(已办证职工数/应办证职工数 * 100%)
				sqlsb.append( "select 0 from dual union all ");
				//18 独生子女情况 现有独生子女数 家庭子女数:fb.tbfi_children_num
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb where emp.tbe_id=fb.tbe_id and emp.org_id='" ).append( curOrgId ).append( "' and fb.tbfi_valid='1' and fb.tbfi_children_num=1 and ").append(notCountEmployeeSql).append(" union all ");
				
				//19 四术情况 人流
				sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-1' and ").append(notCountEmployeeSql).append(" union all ");
				//20 四术情况 上环
				sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='109-2' and ").append(notCountEmployeeSql).append(" union all ");
				//21 四术情况 结扎
				sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='109-3' and ").append(notCountEmployeeSql).append(" union all ");
				//22 四术情况 引产 中期
				sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-2' and ").append(notCountEmployeeSql).append(" union all ");
				//23 四术情况 引产 大月份
				sqlsb.append( "select count(tbcsr.tbe_id) from tb_employee emp , tbc_surgery_record tbcsr where emp.tbe_id=tbcsr.tbe_id and tbcsr.org_id='" ).append( curOrgId ).append( "' and tbcsr.reserve5='' and tbcsr.tbcsr_type='110-3' and ").append(notCountEmployeeSql).append(" union all ");
				//24 办理再生育证明数
				sqlsb.append( "select count(emp.tbe_id) from tb_employee emp , tb_family_base fb , tbe_faa_birth_record tbefbr where ((emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='男') or (emp.tbe_id=fb.tbe_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='0') or (emp.tbe_id=fb.tbe_spouse_id and fb.tbfi_spouse_sex='女' and fb.tbfi_spouse_is_join='1')) and fb.tbfi_id=tbefbr.tbfi_id and tbefbr.tbefbr_type='1' and emp.org_id='").append(curOrgId).append("' and tbefbr.reserve5='' and ").append(notCountEmployeeSql);

				List<Object> list = AccessDataBaseUtil.query4List(sqlsb.toString(), null);
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao2 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append(curOrgId).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0         1          2         3           4           5          6            7               8               
				e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,
				9               10             11            12              13             14                    15          
				e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,
				16           17               18        19        20        21          22             23  
				e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms
				*/
				insertSql.append(list.get(0).toString()).append(",");
				insertSql.append(list.get(1).toString()).append(",");
				insertSql.append(list.get(2).toString()).append(",");
				insertSql.append(list.get(3).toString()).append(",");
				insertSql.append(list.get(4).toString()).append(",");
				// 晚婚率
				int jhrs = Integer.parseInt(list.get(3).toString());
				int whrs = Integer.parseInt(list.get(4).toString());
				insertSql.append(jhrs == 0 ? df.format(0.00) : df.format((float)(whrs * 100)/jhrs)).append(",");
				insertSql.append(list.get(6).toString()).append(",");
				insertSql.append(list.get(7).toString()).append(",");
				insertSql.append(list.get(8).toString()).append(",");
				insertSql.append(list.get(9).toString()).append(",");
				insertSql.append(list.get(10).toString()).append(",");
				// 晚育率
				int wyrs = Integer.parseInt(list.get(10).toString());
				int jhnyt = Integer.parseInt(list.get(7).toString());
				insertSql.append(jhnyt == 0 ? df.format(0.00) : df.format((float)(wyrs * 100)/jhnyt)).append(",");
				// 计划生育率
				insertSql.append("100").append(",");
				insertSql.append(list.get(13).toString()).append(",");
				insertSql.append(list.get(14).toString()).append(",");
				insertSql.append(list.get(15).toString()).append(",");
				// 独生子女办证率
				int yingbanzheng = Integer.parseInt(list.get(14).toString());
				int yibanzheng = Integer.parseInt(list.get(15).toString());
				insertSql.append(yingbanzheng == 0 ? df.format(0.00) : df.format((float)(yibanzheng * 100)/yingbanzheng)).append(",");
				insertSql.append(list.get(17).toString()).append(",");
				insertSql.append(list.get(18).toString()).append(",");
				insertSql.append(list.get(19).toString()).append(",");
				insertSql.append(list.get(20).toString()).append(",");
				insertSql.append(list.get(21).toString()).append(",");
				insertSql.append(list.get(22).toString()).append(",");
				insertSql.append(list.get(23).toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
				
				insertSqls.add("update tbe_neonate_main set reserve5='" + curOrgId + "-" + year + "-" + quarter + "' where org_id='" + curOrgId + "' and reserve5='' ");
				insertSqls.add("update tbc_surgery_record set reserve5='" + curOrgId + "-" + year + "-" + quarter + "' where org_id='" + curOrgId + "' and reserve5='' ");
				insertSqls.add("update tbe_faa_birth_record set reserve5='" + curOrgId + "-" + year + "-" + quarter + "' where org_id='" + curOrgId + "' and reserve5='' ");
				insertSqls.add("update tb_employee set reserve5='" + curOrgId + "-" + year + "-" + quarter + "' where org_id='" + curOrgId + "' and reserve5='1' ");
			}
			
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 上报非叶子（父级）机构的报表一数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitUnleafBaobiao1(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			List<Object[]> tempList;
			Object[] tempArray;
			for(Object id : ids){
				String sql = "select sum(e_heji),sum(e_nan),sum(e_nv),sum(e_yihun_nan),sum(e_yihun_nv),sum(e_weihun_nan),sum(e_weihun_nv),sum(e_yhyl_heji),sum(e_yhyl_nan),sum(e_yhyl_nv),sum(e_yhyl_wsy_heji),sum(e_yhyl_wsy_nan),sum(e_yhyl_wsy_nv),sum(e_yhyl_ysy_heji),sum(e_yhyl_ysy_nan),sum(e_yhyl_ysy_nv),sum(e_ylsjycs_heji),sum(e_ylsjycs_yls_heji),sum(e_ylsjycs_yls_jz ),sum(e_ylsjycs_yls_sh),sum(e_ylsjycs_yls_pm),sum(e_ylsjycs_yls_gj),sum(e_ylsjycs_yls_ym),sum(e_ylsjycs_yls_fy),sum(e_ylsjycs_yls_qt),sum(e_ylsjycs_wls),sum(e_ylsjycs_lsl),sum(e_hyrs),sum(e_dyrs),sum(e_bjcs) from tb_rkyjhsy_baobiao1 where org_id in (select d.department_id from t_department d where d.dparent_id=?)";
				tempList = AccessDataBaseUtil.query(sql, new String[]{(String)id});
				tempArray = tempList.get(0);
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao1 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append((String)id).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0      1     2    3           4          5            6           7           8          9         
				e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,
				10              11             12            13              14             15            16             
				e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,
				17                 18                19               20               21               22               
				e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,
				23               24               25            26            27     28     29                 
				e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs
				*/
				insertSql.append(tempArray[0].toString()).append(",");
				insertSql.append(tempArray[1].toString()).append(",");
				insertSql.append(tempArray[2].toString()).append(",");
				insertSql.append(tempArray[3].toString()).append(",");
				insertSql.append(tempArray[4].toString()).append(",");
				insertSql.append(tempArray[5].toString()).append(",");
				insertSql.append(tempArray[6].toString()).append(",");
				insertSql.append(tempArray[7].toString()).append(",");
				insertSql.append(tempArray[8].toString()).append(",");
				insertSql.append(tempArray[9].toString()).append(",");
				//无生育合计
				insertSql.append(Integer.parseInt(tempArray[11].toString()) + Integer.parseInt(tempArray[12].toString())).append(",");
				insertSql.append(tempArray[11].toString()).append(",");
				insertSql.append(tempArray[12].toString()).append(",");
				insertSql.append(tempArray[13].toString()).append(",");
				insertSql.append(tempArray[14].toString()).append(",");
				insertSql.append(tempArray[15].toString()).append(",");
				// 应落实节育措施人数
				int yls = Integer.parseInt(tempArray[16].toString());
				insertSql.append(yls).append(",");
				// 已落实节育措施人数
				int ylsrs = Integer.parseInt(tempArray[18].toString()) + 
							Integer.parseInt(tempArray[19].toString()) + 
							Integer.parseInt(tempArray[20].toString()) + 
							Integer.parseInt(tempArray[21].toString()) + 
							Integer.parseInt(tempArray[22].toString()) + 
							Integer.parseInt(tempArray[23].toString()) +
							Integer.parseInt(tempArray[24].toString());
				insertSql.append(ylsrs).append(",");
				insertSql.append(tempArray[18].toString()).append(",");
				insertSql.append(tempArray[19].toString()).append(",");
				insertSql.append(tempArray[20].toString()).append(",");
				insertSql.append(tempArray[21].toString()).append(",");
				insertSql.append(tempArray[22].toString()).append(",");
				insertSql.append(tempArray[23].toString()).append(",");
				insertSql.append(tempArray[24].toString()).append(",");
				insertSql.append(tempArray[25].toString()).append(",");
				// 节育措施落实率
				insertSql.append(yls == 0 ? df.format(0.00) : df.format((float)(ylsrs * 100)/yls)).append(",");
				insertSql.append(tempArray[27].toString()).append(",");
				insertSql.append(tempArray[28].toString()).append(",");
				insertSql.append(tempArray[29].toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
			}
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 上报非叶子（父级）机构的报表二数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitUnleafBaobiao2(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			List<Object[]> tempList;
			Object[] tempArray;
			for(Object id : ids){
				String sql = "select sum(e_zgrs_hj),sum(e_zgrs_nan),sum(e_zgrs_nv),sum(e_hyzk_jhrs),sum(e_hyzk_whrs),sum(e_hyzk_whl),sum(e_syqk_csyes),sum(e_syqk_qz_jhnyt),sum(e_syqk_qz_jhnet),sum(e_syqk_qz_jhwcs),sum(e_syqk_qz_wyrs),sum(e_syqk_qz_wyl),sum(e_syqk_qz_jhsyl),sum(e_syqk_qz_hyrs),sum(e_dsznqk_yingbanzheng),sum(e_dsznqk_yibanzheng),sum(e_dsznqk_bzl),sum(e_dsznqk_xydszns),sum(e_ssqk_rl),sum(e_ssqk_sh),sum(e_ssqk_jz),sum(e_ssqk_yc_zq),sum(e_ssqk_yc_dyf),sum(e_blzsyzms) from tb_rkyjhsy_baobiao2 where org_id in (select d.department_id from t_department d where d.dparent_id=?)";
				tempList = AccessDataBaseUtil.query(sql, new String[]{(String)id});
				tempArray = tempList.get(0);
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao2 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append((String)id).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0         1          2         3           4           5          6            7               8               
				e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,
				9               10             11            12              13             14                    15          
				e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,
				16           17               18        19        20        21          22             23  
				e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms
				*/
				insertSql.append(tempArray[0].toString()).append(",");
				insertSql.append(tempArray[1].toString()).append(",");
				insertSql.append(tempArray[2].toString()).append(",");
				insertSql.append(tempArray[3].toString()).append(",");
				insertSql.append(tempArray[4].toString()).append(",");
				// 晚婚率
				int jhrs = Integer.parseInt(tempArray[3].toString());
				int whrs = Integer.parseInt(tempArray[4].toString());
				insertSql.append(jhrs == 0 ? df.format(0.00) : df.format((float)(whrs * 100)/jhrs)).append(",");
				insertSql.append(tempArray[6].toString()).append(",");
				insertSql.append(tempArray[7].toString()).append(",");
				insertSql.append(tempArray[8].toString()).append(",");
				insertSql.append(tempArray[9].toString()).append(",");
				insertSql.append(tempArray[10].toString()).append(",");
				// 晚育率
				int wyrs = Integer.parseInt(tempArray[10].toString());
				int jhnyt = Integer.parseInt(tempArray[7].toString());
				insertSql.append(jhnyt == 0 ? df.format(0.00) : df.format((float)(wyrs * 100)/jhnyt)).append(",");
				// 计划生育率
				insertSql.append("100").append(",");
				insertSql.append(tempArray[13].toString()).append(",");
				insertSql.append(tempArray[14].toString()).append(",");
				insertSql.append(tempArray[15].toString()).append(",");
				// 独生子女办证率
				int yingbanzheng = Integer.parseInt(tempArray[14].toString());
				int yibanzheng = Integer.parseInt(tempArray[15].toString());
				insertSql.append(yingbanzheng == 0 ? df.format(0.00) : df.format((float)(yibanzheng * 100)/yingbanzheng)).append(",");
				insertSql.append(tempArray[17].toString()).append(",");
				insertSql.append(tempArray[18].toString()).append(",");
				insertSql.append(tempArray[19].toString()).append(",");
				insertSql.append(tempArray[20].toString()).append(",");
				insertSql.append(tempArray[21].toString()).append(",");
				insertSql.append(tempArray[22].toString()).append(",");
				insertSql.append(tempArray[23].toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
			}
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 上报年度报表一数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitNianBaobiao1(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			List<Object[]> tempList;
			Object[] tempArray;
			for(Object id : ids){
				StringBuffer sql = new StringBuffer("select e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji, ") 
				.append("e_yhyl_nan,e_yhyl_nv,e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,e_ylsjycs_yls_heji, ")
				.append("e_ylsjycs_yls_jz,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,(select sum(e_bjcs) from tb_rkyjhsy_baobiao1 where org_id='").append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1) ") 
				.append("from tb_rkyjhsy_baobiao1 where org_id='").append((String)id).append("' and bb_year=").append(year).append(" and bb_quarter=4");
				
				tempList = AccessDataBaseUtil.query(sql.toString(), null);
				tempArray = tempList.get(0);
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao1 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append((String)id).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0      1     2    3           4          5            6           7           8          9         
				e_heji,e_nan,e_nv,e_yihun_nan,e_yihun_nv,e_weihun_nan,e_weihun_nv,e_yhyl_heji,e_yhyl_nan,e_yhyl_nv,
				10              11             12            13              14             15            16             
				e_yhyl_wsy_heji,e_yhyl_wsy_nan,e_yhyl_wsy_nv,e_yhyl_ysy_heji,e_yhyl_ysy_nan,e_yhyl_ysy_nv,e_ylsjycs_heji,
				17                 18                19               20               21               22               
				e_ylsjycs_yls_heji,e_ylsjycs_yls_jz ,e_ylsjycs_yls_sh,e_ylsjycs_yls_pm,e_ylsjycs_yls_gj,e_ylsjycs_yls_ym,
				23               24               25            26            27     28     29                 
				e_ylsjycs_yls_fy,e_ylsjycs_yls_qt,e_ylsjycs_wls,e_ylsjycs_lsl,e_hyrs,e_dyrs,e_bjcs
				*/
				insertSql.append(tempArray[0].toString()).append(",");
				insertSql.append(tempArray[1].toString()).append(",");
				insertSql.append(tempArray[2].toString()).append(",");
				insertSql.append(tempArray[3].toString()).append(",");
				insertSql.append(tempArray[4].toString()).append(",");
				insertSql.append(tempArray[5].toString()).append(",");
				insertSql.append(tempArray[6].toString()).append(",");
				insertSql.append(tempArray[7].toString()).append(",");
				insertSql.append(tempArray[8].toString()).append(",");
				insertSql.append(tempArray[9].toString()).append(",");
				//无生育合计
				insertSql.append(Integer.parseInt(tempArray[11].toString()) + Integer.parseInt(tempArray[12].toString())).append(",");
				insertSql.append(tempArray[11].toString()).append(",");
				insertSql.append(tempArray[12].toString()).append(",");
				insertSql.append(tempArray[13].toString()).append(",");
				insertSql.append(tempArray[14].toString()).append(",");
				insertSql.append(tempArray[15].toString()).append(",");
				// 应落实节育措施人数
				int yls = Integer.parseInt(tempArray[16].toString());
				insertSql.append(yls).append(",");
				// 已落实节育措施人数
				int ylsrs = Integer.parseInt(tempArray[18].toString()) + 
							Integer.parseInt(tempArray[19].toString()) + 
							Integer.parseInt(tempArray[20].toString()) + 
							Integer.parseInt(tempArray[21].toString()) + 
							Integer.parseInt(tempArray[22].toString()) + 
							Integer.parseInt(tempArray[23].toString()) +
							Integer.parseInt(tempArray[24].toString());
				insertSql.append(ylsrs).append(",");
				insertSql.append(tempArray[18].toString()).append(",");
				insertSql.append(tempArray[19].toString()).append(",");
				insertSql.append(tempArray[20].toString()).append(",");
				insertSql.append(tempArray[21].toString()).append(",");
				insertSql.append(tempArray[22].toString()).append(",");
				insertSql.append(tempArray[23].toString()).append(",");
				insertSql.append(tempArray[24].toString()).append(",");
				insertSql.append(tempArray[25].toString()).append(",");
				// 节育措施落实率
				insertSql.append(yls == 0 ? df.format(0.00) : df.format((float)(ylsrs * 100)/yls)).append(",");
				insertSql.append(tempArray[27].toString()).append(",");
				insertSql.append(tempArray[28].toString()).append(",");
				insertSql.append(tempArray[29].toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
			}
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 上报年度报表二数据
	 * @param ids
	 * 需要上报的部门ID
	 * @param type
	 * 报表类型1：季报，2：年报
	 * @param year
	 * 年份
	 * @param quarter
	 * 季度
	 * @throws Exception
	 */
	private void submitNianBaobiao2(List<Object> ids, int type, int year, int quarter) throws Exception{
		if(ids != null && ids.size() > 0){
			//获取当前用户对象
			UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
			
		    List<String> insertSqls = new ArrayList<String>();
		    Calendar cal = Calendar.getInstance();
		    int nowMonth = cal.get(Calendar.MONTH) + 1;
		    int nowDay = cal.get(Calendar.DATE);
		    // 格式化小数
			DecimalFormat df = new DecimalFormat("0.##");
			List<Object[]> tempList;
			Object[] tempArray;
			for(Object id : ids){
				StringBuffer sql = new StringBuffer("select e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,")
       			.append("(select sum(e_hyzk_jhrs) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_hyzk_whrs) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("e_hyzk_whl,")
       			.append("(select sum(e_syqk_csyes) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_syqk_qz_jhnyt) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_syqk_qz_jhnet) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_syqk_qz_jhwcs) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_syqk_qz_wyrs) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("0,'100',e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,")
       			.append("e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,")
       			.append("(select sum(e_ssqk_rl) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_ssqk_sh) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_ssqk_jz) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_ssqk_yc_zq) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_ssqk_yc_dyf) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1),")
       			.append("(select sum(e_blzsyzms) from tb_rkyjhsy_baobiao2 where org_id='")
       			.append((String)id).append("' and bb_year=").append(year).append(" and bb_type=1) ")
       			.append("from tb_rkyjhsy_baobiao2 where org_id='").append((String)id).append("' and bb_year=").append(year).append(" and bb_quarter=4");
				tempList = AccessDataBaseUtil.query(sql.toString(), null);
				tempArray = tempList.get(0);
				StringBuffer insertSql = new StringBuffer("");
				insertSql.append("insert into tb_rkyjhsy_baobiao2 (bb_id,date_month,date_day,tbr_name,tbr_tel,org_id,bb_type,bb_year,bb_quarter,e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms,create_by,create_time) ");
				insertSql.append("values (");
				insertSql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
				insertSql.append(nowMonth).append(",");
				insertSql.append(nowDay).append(",");
				insertSql.append("'").append(user.getUserrelName()).append("',");
				insertSql.append("'").append("").append("',");
				insertSql.append("'").append((String)id).append("',");
				insertSql.append("'").append(type).append("',");
				insertSql.append(year).append(",");
				insertSql.append(quarter).append(",");
				/*
				0         1          2         3           4           5          6            7               8               
				e_zgrs_hj,e_zgrs_nan,e_zgrs_nv,e_hyzk_jhrs,e_hyzk_whrs,e_hyzk_whl,e_syqk_csyes,e_syqk_qz_jhnyt,e_syqk_qz_jhnet,
				9               10             11            12              13             14                    15          
				e_syqk_qz_jhwcs,e_syqk_qz_wyrs,e_syqk_qz_wyl,e_syqk_qz_jhsyl,e_syqk_qz_hyrs,e_dsznqk_yingbanzheng,e_dsznqk_yibanzheng,
				16           17               18        19        20        21          22             23  
				e_dsznqk_bzl,e_dsznqk_xydszns,e_ssqk_rl,e_ssqk_sh,e_ssqk_jz,e_ssqk_yc_zq,e_ssqk_yc_dyf,e_blzsyzms
				*/
				insertSql.append(tempArray[0].toString()).append(",");
				insertSql.append(tempArray[1].toString()).append(",");
				insertSql.append(tempArray[2].toString()).append(",");
				insertSql.append(tempArray[3].toString()).append(",");
				insertSql.append(tempArray[4].toString()).append(",");
				// 晚婚率
				int jhrs = Integer.parseInt(tempArray[3].toString());
				int whrs = Integer.parseInt(tempArray[4].toString());
				insertSql.append(jhrs == 0 ? df.format(0.00) : df.format((float)(whrs * 100)/jhrs)).append(",");
				insertSql.append(tempArray[6].toString()).append(",");
				insertSql.append(tempArray[7].toString()).append(",");
				insertSql.append(tempArray[8].toString()).append(",");
				insertSql.append(tempArray[9].toString()).append(",");
				insertSql.append(tempArray[10].toString()).append(",");
				// 晚育率
				int wyrs = Integer.parseInt(tempArray[10].toString());
				int jhnyt = Integer.parseInt(tempArray[7].toString());
				insertSql.append(jhnyt == 0 ? df.format(0.00) : df.format((float)(wyrs * 100)/jhnyt)).append(",");
				// 计划生育率
				insertSql.append("100").append(",");
				insertSql.append(tempArray[13].toString()).append(",");
				insertSql.append(tempArray[14].toString()).append(",");
				insertSql.append(tempArray[15].toString()).append(",");
				// 独生子女办证率
				int yingbanzheng = Integer.parseInt(tempArray[14].toString());
				int yibanzheng = Integer.parseInt(tempArray[15].toString());
				insertSql.append(yingbanzheng == 0 ? df.format(0.00) : df.format((float)(yibanzheng * 100)/yingbanzheng)).append(",");
				insertSql.append(tempArray[17].toString()).append(",");
				insertSql.append(tempArray[18].toString()).append(",");
				insertSql.append(tempArray[19].toString()).append(",");
				insertSql.append(tempArray[20].toString()).append(",");
				insertSql.append(tempArray[21].toString()).append(",");
				insertSql.append(tempArray[22].toString()).append(",");
				insertSql.append(tempArray[23].toString()).append(",");
				
				insertSql.append("'").append(user.getUserId()).append("',");
				insertSql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
				
				insertSql.append(")");
				insertSqls.add(insertSql.toString());
			}
			AccessDataBaseUtil.executeSQLUpdate(insertSqls);
		}
	}
	
	/**
	 * 获取制定季度或年度，所有子部门中已经上报的部门数量
	 * @param subOrgIds
	 * 子部门ID字符串，格式为'id1','id2','id3'
	 * @param bbYear
	 * 报表年份
	 * @param bbQuarter
	 * 报表月份
	 * @return
	 * 已经上报的部门数量
	 * @throws Exception
	 */
	private int getSubOrgSubmitCount(String subOrgIds, String bbYear, String bbQuarter) throws Exception{
		//String curOrgRecordSql = "select count(bb.bb_id) from tb_rkyjhsy_baobiao1 bb where bb.org_id in(?) and bb.bb_year=? and bb.bb_quarter=? ";
		StringBuffer subOrgSubmitCountSql = new StringBuffer("select count(bb.bb_id) from tb_rkyjhsy_baobiao2 bb where bb.org_id in(").append(subOrgIds).append(") and bb.bb_year=").append(bbYear).append(" and bb.bb_quarter=").append(bbQuarter);
		return AccessDataBaseUtil.queryForInt(subOrgSubmitCountSql.toString(),null);
	}
	
	/**
	 * 获取指定部门的下级子部门数量
	 * @param parentId
	 * （父）部门ID
	 * @return
	 * 下级子部门数量
	 * @throws Exception
	 */
	private int getSubOrgCount(String parentId) throws Exception{
		String subOrgCountSql = "select count(d.department_id) from t_department d where d.dparent_id=?";
		return AccessDataBaseUtil.queryForInt(subOrgCountSql, new String[]{parentId});
	}
	
	/**
	 * 发布公告
	 * @param user
	 * 公告发布人（UserBean实例）
	 * @param content
	 * 公告内容
	 * @param traceKey
	 * 公告追溯键值，用来标识该通知属于哪个模块、哪个功能等信息
	 * @param type
	 * 公告类型：1单独公告；2关联公告
	 * @param time
	 * 公告持续时间（单位小时），为0则表示永久有效，此时需要程序控制公告失效状态
	 * @return
	 * 公告ID
	 * @throws Exception
	 */
	private String sendAnnouncement(UserBean user, String content, String traceKey, int type, int time) throws Exception{
		StringBuffer sql = new StringBuffer();
		String tmaid = AccessDataBaseUtil.getPKUUID();
		sql.append("insert into TM_ANNOUNCEMENT (TMA_ID,TMA_FROM,TMA_CONTENT,TMA_STATUS,TMA_TYPE,TMA_TIME,TMA_TRACE_KEY,CREATE_BY,CREATE_TIME) ");
		sql.append("values (");
		sql.append("'").append(tmaid).append("',");
		sql.append("'").append(user.getCurOrgId()).append("',");
		sql.append("'").append(content).append("',");
		sql.append("1").append(",");
		sql.append(type).append(",");
		sql.append(time).append(",");
		sql.append("'").append(traceKey).append("',");
		sql.append("'").append(user.getUserId()).append("',");
		sql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
		sql.append(")");
		
		AccessDataBaseUtil.executeSQLUpdate(sql.toString());
		return tmaid;
	}
	
	/**
	 * 发布通知
	 * @param user
	 * 当前操作用户的UserBean
	 * @param toOrgs
	 * 通知接收部门ID列表（List<String>）
	 * @param content
	 * 通知内容
	 * @param tmaid
	 * 关联公告ID，无关联则写0
	 * @param traceKey
	 * 通知追溯键值，用来标识该通知属于哪个模块、哪个功能等信息
	 * @param time
	 * 通知持续时间（单位小时），为0则表示只显示一次
	 * @throws Exception
	 */
	private void sendNotice(UserBean user, String[] toOrgs, String content, String tmaid, String traceKey, int time) throws Exception{
		List<String> sqlList = new ArrayList<String>();
		for(String to : toOrgs) {
			StringBuffer sql = new StringBuffer();
			sql.append("insert into TM_NOTICE (TMN_ID,TMN_FROM,TMN_TO,TMN_CONTENT,TMN_STATUS,TMN_TIME,TMN_TRACE_KEY,TMA_ID,CREATE_BY,CREATE_TIME) ");
			sql.append("values (");
			sql.append("'").append(AccessDataBaseUtil.getPKUUID()).append("',");
			sql.append("'").append(user.getCurOrgId()).append("',");
			sql.append("'").append(to).append("',");
			sql.append("'").append(content).append("',");
			sql.append("1").append(",");
			sql.append(time).append(",");
			sql.append("'").append(traceKey).append("',");
			sql.append("'").append(tmaid).append("',");
			sql.append("'").append(user.getUserId()).append("',");
			sql.append("'").append(FormatUtil.getCurentDateTime()).append("'");
			sql.append(")");
			
			sqlList.add(sql.toString());
		}
		AccessDataBaseUtil.executeSQLUpdate(sqlList);
	}
	
	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	

}
